How can I calculate the Rate Of Change between two periods at an Aggregate Level

[ROBERT_J_ESTEVES]

Given the following example table below, how can I calculate the growth or decline at the manager level dynamically using a dashboard calculated field? Also, How can I display the results in a dashboard KPI?

To calculate the Growth or decline I need to use the following formula:

and here is an example of how I solved the problem the problem on e-paper :

Based on my paper solution, I then tried to replicate the logic in Quick Sight using the sumOver() function and parameters ( start_date / end_date, manager).

My goal is to use the two variables as the source for my KPI.

But, when try to create the KPI using the two variables no data is displayed. How can resolve this issue?

[Max]

It looks like your two calculations are the same.

Also can you show if either of them render data?

And to confirm your end_date and start_dates are parameters?

I might look to take away the partitions and instead use filters linked to those parameters.

[ROBERT_J_ESTEVES]

Max,
Thanks for taking the time to help me with this issue.
Yes, the start and end dates are parameters. Here is a screenshot of the Quick Sight Calculated fields pane.

Both fields render data, but the Growth/Decline calculation will not render.

[Jesse]

Hi @ROBERT_J_ESTEVES - I think you are on the right track, but instead of using sumOver, you can just use Sum. sumOver actually results in a non-aggregate field, so when you do your comparisons to build your ratio, that is happening at a row level, and there arent values for two different periods on the same row, which is why you are prob getting nulls.

if you are indeed defining your start and end periods as single days (based on your two parameters), then do these 3 calcs:
Sales-Start: ifelse(date=${startParam}, sales, 0)
Sales-End: ifelse(date=${endParam}, sales, 0)
%Diff: (sum(Sales-End) - sum(Sales-Start) )/ sum(Sales-Start)

Does that work for you?