Calculating Percentiles based on historical data

Question

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!

Calculate Percentiles.xlsx

AbhilashR · Accepted Answer

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!