has anyone experienced the following error when using required_fields in a measure and then trying to pivot in a visualization?:
Incompatible Data
Unable to chart pivoted data when dimensionhas repeated values. This commonly happens when you create a query with a second dimension and hide that dimension from the chart.
to me, it seems the the required_fields shouldnt really matter for the pivot because the pivoting/transposing operations take place outside the inner-most explore query and thus if the required_fields fields are not selected as part of the visualization they shouldnt impact the ability to pivot?
I could be crazy but this might be related to the following post from a year or two ago:
Understand the workaround. Problem is that we hide the aggregation from the user like the example at the top so they will not be aware that they would need to employ this workaround.
I’ve been fiddling with this and I think i may have found a workaround but it’s not exactly the most dynamic solution as all possible fields that I may want to aggregate with respect to have to be hardcoded into the liquid template for aggregation_dimension…
dimension_group: asof {
type: time
timeframes: [raw, date, month, quarter, year]
sql: ${TABLE}.asofdate ;;
# allow_fill: no
}
dimension: entityuid { sql: ${TABLE}.entityuid ;;}
dimension: entityname { sql: ${TABLE}.entityname ;;}
dimension: entitycode { sql: ${TABLE}.entitycode ;;}
dimension: aggregation_dimension {
# this allows you to pivot on a dimension and
# the measure will reflect contributions to the total
# with respect to selected/pivoted dimensions
type: string
sql:
{% if view1.entitycode._is_selected %}
${view1.entitycode}
{% elsif view1.entityname._is_selected %}
${view1.entityname}
{% else %}
${view1.entityuid}
{% endif %}
;;
}
dimension: measure_basis {
type: number
sql: ${amount_usd} ;;
}
measure: measure_contribution_numerator {
type: sum
sql: (${measure_basis}) ;;
}
measure: measure_contribution_denominator {
type: number
sql: nullif(coalesce(sum(sum(${measure_basis})) over (partition by ${asof_raw}, ${aggregation_dimension}),0),0) ;;
required_fields: [asof_raw]
}
measure: measure_contribution {
type: number
sql: (${measure_contribution_numerator} / ${measure_contribution_denominator});;
value_format_name: percent_1
}
Hey @mplooker,
This is a great pattern to allow for pivoting on a measure using aggregate awareness, and I think it’s your best bet here. Since the required_fields
are going to change how the measure is aggregated, they will affect how pivots are calculated so partitioning by the required field and also the pivot dimension is a good way to handle this issue.
Best,
Ryan
Hi @mplooker - have you tried a percent_of_total
measure? Although it won’t handle all use cases, it sounds like it might be what you are trying to do.