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

Question & Answer
,
1

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?
image
To calculate the Growth or decline I need to use the following formula:
image
and here is an example of how I solved the problem the problem on e-paper :slight_smile: :
image
image
Based on my paper solution, I then tried to replicate the logic in QuickSight using the sumOver() function and parameters ( start_date / end_date, manager).

image
image772×120 3.34 KB

My goal is to use the two variables as the source for my KPI.
image
image956×101 1.27 KB

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

2

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.

3

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 QuickSight Calculated fields pane.

image
image776×364 9.7 KB

Both fields render data, but the Growth/Decline calculation will not render.
image
image1421×392 60.8 KB

image
image507×517 21.4 KB

image
image514×543 21.9 KB

4

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?