Incorrect calculation of the total amount using the window function

Question & Answer
, , ,
1

I would like to calculate the daily spending of the planned budget using three formulas:

  1. The first formula calculates the budget planned for a particular month (the values are the same for all dates within a given month):

    Budget: sum(min({budget_sum_month}, [campaign, internal_id, month]))

  2. The second formula calculates the cumulative amount of funds spent:

    Spend_RunningSum: runningSum(sum({Spend}), [ {event_date} ASC], [campaign, internal_id, month])

  3. The third formula calculates the ratio of expenses to the budget:

    Spending: Spend_RunningSum/Budget

The problem is that the third formula gives incorrect results for the subtotals and the grand total.

image
image682Ă—635 69.7 KB

I realized that the issue lies in the “Budget” formula. I tried to change it to:

sum(minOver({budget_sum_this_month}, [campaign, {internal_id}, Month], PRE_AGG)). The result is the same.

Could you please help me?

1 Like
2

When I use this formula: runningSum(sum(spend), [ {event_date} ASC], [campaign, {internal_id}, Month])/ minOver(min({budget_sum_month}), [campaign, {internal_id}, Month]) , I get a different result, but it is not yet 100% correct.
the result is correct for the month level, but for next “Customer” lever it was given as minOver(min()) as expected.

image
image872Ă—241 26.2 KB

It makes sense that I need to use sumOver(min(),…):

runningSum(sum(spend), [ {event_date} ASC], [campaign, {internal_id}, Month])/ sumOver(min({budget_sum_month}), [campaign, {internal_id}, Month]) , but the result is incorrect:

image
image701Ă—174 20.3 KB

It doesn’t work as sumOver(min({budget_sum_month})…), but works like sum({budget_sum_month}). 155,520 = 6480*24, 24 - the number of rows.

1 Like
4

Hello @Ekaterina, so, one thing to consider when using partitioned calculated fields within a pivot table is that it can cause issues with the field value compared to the total. It may even be unnecessary to use partition fields if your pivot table is already set up with the parititon values in the Row field wells.

The best way for me to assist you with a solution would be if you could set up a demo version of this analysis in QuickSight Arena and share a link to the dashboard so I can try and test out some options.

I am also a little unsure of how the partitioned runningSum is aggregating with a nested partitioned sum/minOver function. Maybe there would be a way to use windowSum functions for both the numerator and denominator values. It often helps if the aggregation type matches to ensure they work together as expected.

I can try to guide you further with a little more information, but let me know if this helps!

5

Hello @Ekaterina, I wanted to check in since we have not received a response from you. If you still need assistance with this issue in QuickSight, please follow-up with some more information or a dashboard in Arena so I can assist you further.

If we do not hear back from you in 3 days, I will archive this topic. Thank you!

6

Hello @Ekaterina, since we have not heard back from you, I will archive this topic. If you still need assistance with this issue, please post a new question in the community and link to this topic to provide relevant information. That will ensure you are at the top of the priority list for a response from one of our QuickSight experts.

Thank you!