Aggregation Error When Creating “Last Period” Measure Using sum(wins)/sum(bets) in Pivot Table

Q&A
,
1

I am trying to create a calculated field in QuickSight that returns a value only for the last period (based on the user-selected DATE_BREAKDOWN: Daily / Weekly / Monthly / Hourly).

The following calculated field works correctly when the return value is a raw field (WINS_DOLLAR_SC).
This field allows me to sort the pivot by the latest period only:

ifelse(
{DATE_BREAKDOWN}= ‘Daily’ AND PERIOD = addDateTime(-1, ‘DD’, truncDate(‘DD’, now())),
{WINS_DOLLAR_SC},
ifelse(
{DATE_BREAKDOWN} = ‘Weekly’ AND truncDate(‘WK’,PERIOD) = truncDate(‘WK’, addDateTime(-1, ‘DD’, now())),
{WINS_DOLLAR_SC},
ifelse(
{DATE_BREAKDOWN} = ‘Monthly’ AND truncDate(‘MM’,PERIOD) = truncDate(‘MM’, addDateTime(-1, ‘DD’, now())),
{WINS_DOLLAR_SC},
ifelse(
{DATE_BREAKDOWN}= ‘Hourly’ AND PERIOD = addDateTime(-1, ‘HH’, truncDate(‘HH’, now())),
{WINS_DOLLAR_SC},
NULL
)
)
)
)

This calculated field works as expected inside a Pivot Table and allows me to sort rows by the “last period only” measure.

When I try to return an aggregated metric instead of a raw field, e.g.:

sum({WINS_DOLLAR_SC}) / sum({BETS_FOR_SOURCE})

Inside the same IFELSE structure, QuickSight shows the following error:

Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.

Even when all conditions evaluate to a single period,
even when PERIOD is fully determined,
and even when the logic is identical except for returning an aggregated measure.

Request:

I would like to understand:

  1. Why this IFELSE logic works when returning a raw field but fails when returning a simple aggregated measure (sum/sum) even though the IFELSE conditions themselves do not contain any aggregations.

  2. What is the correct or recommended way to implement a “last period only” aggregated measure (e.g., sum(wins)/sum(bets)) that can be used for sorting in a Pivot Table, while respecting the DATE_BREAKDOWN logic.

  3. Whether there is any workaround or best practice for calculating this type of “last period metric” in QuickSight without moving the logic to the data warehouse.

1 Like
2

Hello @Eldad_Levi, you should be able to bypass the aggregation error if you switch to LAC-W calculations. I’ll link the documentation for the sumOver calculation: sumOver - Amazon Quick Suite

Since I don’t know the full layout of your pivot table, you may need to add partition values, but if you partition the function with the same fields in the table, we should be able to make this work. You also need to utilize the PRE_AGG calculation level to avoid the mismatch aggregation error. I am also not sure what you called the last period function that returns the WINS_DOLLAR_SC field based on the ifelse statement, but you would probably want to use that in the numerator of the expression. The function would look something like this:

sumOver({Last Period Calculation}, [{Period}], PRE_AGG)/sumOver({BETS_FOR_SOURCE}, [{Period}], PRE_AGG)

If you have more row values or group by fields in your pivot table, you would want to add those fields comma separated into the partition brackets. The sumOver calculation will ignore the group by fields in the table, so you will need to manually add them in. Let me know if this helps!

3

It has worked, Thanks!

1 Like