How to calculate the maximum daily sum across different granularities in QuickSight?

Q&A
, ,
1

Hi! :slight_smile:
I’m looking for a way to display the maximum daily sum on a visual, but for different granularities.

My dataset looks like this:

Date        stock   code
01.01.2025    4      A
01.01.2025    4      B
02.01.2025    1      A
02.01.2025    5      B
03.01.2025    5      A
03.01.2025    1      B

In real-life scenerio there are more than 2 codes and the number of them is dynamic but let’s take this table as an example.
On the chart, I need to show the result for the day when the maximum sum (A+B) occurred within a given period.
For daily granularity, it’s easy because I can use:

sum(
    max({stock}, [code])
)

However, when I change the granularity to e.g. yearly, the calculation is no longer correct.
I get 10 (which is max(A) monthly + max(B) yearly) instead of 8 (the maximum daily sum of A+B within the year, so the value from 01.01.2025).

Could anyone help me find the right calculation for this?
Thanks in advance! :slight_smile:

2

Hi @helseslo,

Whenever you change your granularity, your calculated field will follow; so now that your rows are by year, your calculation will run for those rows.
What if you tried something like the following:
`maxOver(
sumOver({stock}, [Date]),

[code],
PRE_AGG
)`

3

Hello @Brett ! :slight_smile:
Thank you for your answer. Unfortunately, when I tried this formula in a calculated field, I received an error: Execution order mismatch: PRE_FILTER calculations can’t have PRE_Agg operands (in the line with sumOver).

4

Hi @helseslo,
Where’s the PRE_FILTER coming from? is it used in your ‘stock’ or ‘code’ fields?

5

These fields are not aggregated. I think that the problem is the default option for sumOver is POST_AGG_FILTER, because when I change my formula and write it explicitly:

maxOver(
sumOver({stock}, [Date], POST_AGG_FILTER),

[code],
PRE_AGG
)

I got exactly the same error.
But I tried some variations of the formula you pasted and finally I got the result I needed :slight_smile: The final code is:

sumOver(
maxOver({stock}, [{Date}, {code}], PRE_AGG),
[{Date}],
PRE_AGG
)

And the last maximum is applied on the chart as a regular Max aggregation, when we have Date on X-axis and the result of this field as a line :slight_smile:

1 Like