Urgent_Dynamic Percentage Calculation based on 2 columns

Question & Answer
, ,
6

To resolve the “Mismatched aggregation” error in QS, you need to ensure that all parts of your expression use the same level of aggregation. Here’s a straightforward approach to address this:

  1. Separate Aggregations: Create separate calculated fields for each type of value you want to calculate, ensuring consistent use of aggregations.

  2. Review DynamicShelfChangeValue: Ensure all conditions within the ifelse statement use aggregation functions. For example:

    DynamicShelfChangeValue = ifelse(
    ${ShelfChangesValueParam} = "Placements", 
    distinct_count(concat({part_number}, {merchant_name})),
    ${ShelfChangesValueParam} = "Average $ Net Price", 
    avg({net_price}),
    ${ShelfChangesValueParam} = "Average $ Promotion", 
    avg({shelf_price} - {net_price}),
    avg(({shelf_price} - {net_price}) / {shelf_price}) * 100
)

  3. Create Fields for Years: Ensure the fields for the years also use consistent aggregations:

    ValueForYear1 = sum(ifelse({Year} = ${SelectedYear1}, {DynamicShelfChangeValue}, 0))
ValueForYear2 = sum(ifelse({Year} = ${SelectedYear2}, {DynamicShelfChangeValue}, 0))

  4. Calculate the Percentage Change: Now, calculate the percentage change without aggregation errors:

    PercentChange = ifelse(
    ValueForYear1 = 0, 
    null, 
    ((ValueForYear2 - ValueForYear1) / ValueForYear1) * 100
)

By ensuring all parts of the expression use consistent aggregations, you should avoid the “Mismatched aggregation” error. Test these changes and adjust as necessary to ensure the calculations are correct and dynamic.

1 Like