How to find out percentile for certain values in a column

[abhinavrpk]

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. 

Country	Duration	Scenario 1	Scenario 2	Scenario 3	Scenario 4
CANADA	20210822-20210919	3	0
CANADA	20210919-20211017	8	0
CANADA	20211017-20211114	9	0
CANADA	20211114-20211213	11	0
CANADA	20211213-20220110	51	0
CANADA	20220110-20220206	52	8
CANADA	20220206-20220306	54	5
USA	20211022-20211119			5234	3330
USA	20211119-20211217			4788	3266
USA	20211217-20220114			5222	3276
USA	20220114-20220212			4391	2947
USA	20220212-20220311			4397	3083

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

Thanks in advance,
Abhinav 

[danilang · Accepted solution]

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 solution]

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.