Calculating Percentiles based on historical data

[Jain]

While calculating percentiles is simple using the Summarize tool or some of the available macros, I'm having trouble calculating percentiles based on n number of rows for historical data. So in this case if n = 3, for every percentile will be calculated for the above 3 rows/days of data (given Date is sorted in ascending order) grouped by the names. (Sample attached)

Any suggestions?

Thanks in advance!

[AbhilashR · Accepted solution]

Hi @Jain, I chose to use Moving Summarize tool from Crew Macros to compute rolling window percentile calculation. But soon ran into its limitation of not being able to change the Percentile % (default is 50%). So, I went ahead and modified the macro altogether and have included it as part of the package here. 

To change the Percentile %, toggle the range highlighted in the image below.

Once the percentile's are calculated, I shift the date by a day and join the data back to itself to help you get the data in the format you are looking for.

Hope this helps!

[Jain]

Hi @AbhilashR,

Thanks for the solution! This works like a charm.

I made a small modification to the decimal values for Percentile % toggle in the macro because currently it is set to one decimal place and I wanted to calculate percentiles for 1%(0.01) and 99%(0.99) as well.