Required_fields and pivoting

mplooker
Participant IV

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 dimension has 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.

0 3 2,027
3 REPLIES 3

mplooker
Participant IV

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.

Top Labels in this Space
Top Solution Authors