URGENT - Wrong aggregation for sums

[Thomas_F]

I want to make a calculation for volume, mix and pricing effects for our assortment.

I use this formula to calculate the impact on the gross margin for the two compared periods:

• Period 1 = last year previous month (=March 2024)
• Period 2 = last month (= March 2025)

On line level for each SKU it works well with correct values with this formula, but not in total sums aggregated:

(
(
sumIf({Calc. value ordered}, {Order date}< truncDate( ‘MM’, addDateTime(-12,‘MM’, now() ) ) AND {Order date}>= truncDate( ‘MM’, addDateTime(-13,‘MM’, now() ) ) ) / sumIf({# Products ordered}, {Order date}< truncDate( ‘MM’, addDateTime(-12,‘MM’, now() ) ) AND {Order date}>= truncDate( ‘MM’, addDateTime(-13,‘MM’, now() ) ) )

“*”

sumIf({# Products ordered}, {Order date}< truncDate( ‘MM’, addDateTime(-0,‘MM’, now() ) ) AND {Order date}>= truncDate( ‘MM’, addDateTime(-1,‘MM’, now() ) ) )
)

“-”

(
sumIf({Calc. value ordered}, {Order date}< truncDate( ‘MM’, addDateTime(-12,‘MM’, now() ) ) AND {Order date}>= truncDate( ‘MM’, addDateTime(-13,‘MM’, now() ) ) ) / sumIf({# Products ordered}, {Order date}< truncDate( ‘MM’, addDateTime(-12,‘MM’, now() ) ) AND {Order date}>= truncDate( ‘MM’, addDateTime(-13,‘MM’, now() ) ) )

“*”

sumIf({# Products ordered}, {Order date}< truncDate( ‘MM’, addDateTime(-12,‘MM’, now() ) ) AND {Order date}>= truncDate( ‘MM’, addDateTime(-13,‘MM’, now() ) ) )
)
)

/

(
sumIf({Calc. value ordered}, {Order date}< truncDate( ‘MM’, addDateTime(-12,‘MM’, now() ) ) AND {Order date}>= truncDate( ‘MM’, addDateTime(-13,‘MM’, now() ) ) ) / sumIf({# Products ordered}, {Order date}< truncDate( ‘MM’, addDateTime(-12,‘MM’, now() ) ) AND {Order date}>= truncDate( ‘MM’, addDateTime(-13,‘MM’, now() ) ) )

“*”

sumIf({# Products ordered}, {Order date}< truncDate( ‘MM’, addDateTime(-12,‘MM’, now() ) ) AND {Order date}>= truncDate( ‘MM’, addDateTime(-13,‘MM’, now() ) ) )
)

PS: “” need to ignored - without them i always get a bullet list mark in my formula.

I also tried it in different steps with calculated fields, but it also does not work.

I have exported everything to excel and everything is correct except the aggregation (red boxes are the manually calculated values in Excel - red marked the wrong Quicksight calculation.

Could you help me with it?

Thanks in advance!

[duncan]

Hello @Thomas_F

I’m lost on which number set is coming from QuickSight and which is Excel. In the Net sales column, is the total box that is red and equals 185.88% from QuickSight or is it the column that has the red highlight around it?

A workaround I have used in the past specifically with tables is calculating the total on my own and applying that as a field in my table.

[Thomas_F]

Hi @duncan,

many thanks for your reply!

Sorry for the unclear data - the data in the red boxes I have calculated manually and are the correct ones. The numbers in the columns named “effect %” are calculated in QS.

I have also found the reason, why the calculation for every product line is correct but not on aggregated level or in the total sum.

Not only in the line, but also in the sums and aggregation Quicksight applies the same calculation like in the lines. This leads to the point that if the net sales grow by e.g. 50% also the gross profit will change in the same way, because the calculation is not weighted (e.g. product A grows by 75% and product B only by 25% with different margins).

Here is a screenshot from some field in Quicksight and information, what is wrong:

I hope that this is a little bit more understandable.

I think I need a formula to calculate the sums on my own like you mentioned. Could you help me to find a solution for that? Or do you have another idea?

Thanks in advance for your help!

[duncan]

Hey @Thomas_F

Sorry for the late reply!

To create the total you could try something like sumOver({calc_field}, , POST_AGG_FILTER) but I’m not 100% it will work.

Also, could you check that the field well for your calc field has “Totals” set to Sum rather than Default?

[Thomas_F]

Hi @duncan,

many thanks for you feedback and help.

You are completely right - with sumover and it has worked, but with using PRE_AGG.

I still have an issue with SKU, that only have sales in one of the compared periods, but this is caused by the data itself.

Thank you and have a nice day!

Best,
Thomas