Time Series Growth Calculation - Getting Incremental Numbers from Cumulative Total

MPatel · May 12, 2016 5:53PM

Hi,
I have data that's coming in cumulatively:

Date | # of Participants
4/1/2016 | 400
4/2/2016 | 500
4/3/2016 | 625

I want a field that will calculate the difference from the day before. So for 4/2/2016, it would show 100 (500-400). For 4/3/2016 it would show 125 (625-500). Please let me know what formula to use. Thanks!

paola · May 13, 2016 6:13AM

You could use a simple ranking formula to establish the time series day order, then refer to this number when calculating the difference/value growth between the consecutive or non-consecutive days. This formula will rely on all dates in the dataset being different, so the rank values will be increasing by 1 from one date to the next. See the demo file attached.

RANK([Date], [Date], true, false)

IF([Rank date]>1,
SUBSET_SUM([Value], SUBSET([Rank date],[Rank date]))
-
SUBSET_SUM([Value], SUBSET([Rank date],[Rank date]-1)) 
, 
null)

daniel · May 13, 2016 7:27AM

Thought I would offer another implementation for the most simplest case which doesn't need the rank to be setup. It would be to use the dateadd() function to calculate:

[Amount]-SUBSET_SUM([Amount],SUBSET([Date],(DATEADD([Date], -1))))

Ideally the data will have no gaps between consecutive dates.

MPatel · May 13, 2016 11:27AM

Thank you! This worked perfectly!

Welcome!

Categories

Ideas Parade

Tagged