How to find out percentile for certain values in a column

Question

Hi All,

I am very new to Alteryx and trying to find out  a solution to calculate percentile. 
The below data is just an illustration how my table looks like.

I need to calculate Upper and Lower Quartile ( 25 percentile and 75 percentile ). But the catch here is that  we have 19 duration period for each scenarios and we need to calculate the percentile for a range of values ( last 13) , but excluding the latest one. This latest one will be compared to the Interquartile range (IQR) which is calculated once we have first and third quartile values.

CountryDurationScenario 1Scenario 2Scenario 3Scenario 4CANADA20210822-2021091930  CANADA20210919-2021101780  CANADA20211017-2021111490  CANADA20211114-20211213110  CANADA20211213-20220110510  CANADA20220110-20220206528  CANADA20220206-20220306545  USA20211022-20211119  52343330USA20211119-20211217  47883266USA20211217-20220114  52223276USA20220114-20220212  43912947USA20220212-20220311  43973083

Please let me know if anyone knows the right approach to get the desired values.

Thanks in advance,
Abhinav

danilang · Accepted Answer

hi @abhinavrpk

I'm not sure if this is exactly what you're looking for but it should be a good start.

Since you don't have 13 ranges in your sample data, I'm using the last 4 as the ranges to keep.  Transpose them and keep all but the last one and calculate the 25th and 75th percentiles.  Join this up to the last range on country and scenario.

This allows you to compare the last period to the 25th and 75th percentile of the preceding periods.  With your complete data set modify the top two sample tools to be last 13 and the first 12 respectively

Dan

abhinavrpk · Accepted Answer

Hi @danilang

Thank you for the response. Like I said, I am new to Alteryx, but the solution helped me not only with the problem but also gain much more insights into Alteryx as a whole. Thanks a ton.