Median of previous years for the same period

[sibrulotte]

Hi, 

i'd like to do the median value of a ratio.

Table1:

Year,

Month,

Year&Month as Year_month,

Amount1,

Amount2

I have 10 years worth of datat, so 120 lines...

I have a straight table with

Dimension: Year_month

Expression: Amount1/Amount2

I'd like to be able to do the median value of all ratios for a period, using previous years only.

Year_month	Ratio	Median
2011-1	0,3539%
2011-2	0,6369%
2011-3	0,3922%
2011-4	0,3874%
2011-5	0,3680%
2011-6	0,4135%
2011-7	0,2552%
2011-8	0,9637%
2011-9	0,8341%
2011-10	0,7181%
2011-11	0,4170%
2011-12	0,4996%
2012-1	0,3793%
2012-2	0,5727%
2012-3	0,6026%
2012-4	0,5538%
2012-5	0,5367%
2012-6	0,5295%
2012-7	0,6221%
2012-8	1,5954%
2012-9	0,7348%
2012-10	0,5460%
2012-11	0,5014%
2012-12	0,5048%
2013-1	0,4596%
2013-2	0,6201%
2013-3	0,3640%
2013-4	0,3373%
2013-5	0,4443%
2013-6	0,3363%
2013-7	0,2252%
2013-8	0,8969%
2013-9	0,7087%
2013-10	0,5169%
2013-11	0,6330%
2013-12	0,7937%
2014-1	0,3207%
2014-2	0,6478%
2014-3	0,4240%
2014-4	0,3755%
2014-5	0,6258%
2014-6	0,5725%
2014-7	0,3483%
2014-8	1,5641%
2014-9	1,0120%
2014-10	0,6037%
2014-11	0,6718%
2014-12	0,5467%
2015-1	0,3316%
2015-2	0,7538%
2015-3	0,4773%
2015-4	0,4345%
2015-5	0,3700%
2015-6	0,2843%
2015-7	0,2127%
2015-8	1,0427%
2015-9	0,8133%
2015-10	0,6278%
2015-11	0,5514%
2015-12	0,3961%
2016-1	0,3440%
2016-2	0,7844%
2016-3	0,3963%
2016-4	0,4265%
2016-5	0,3431%
2016-6	0,3176%
2016-7	0,2605%
2016-8	1,1486%
2016-9	0,7494%
2016-10	0,5326%
2016-11	0,4334%
2016-12	0,4010%
2017-1	0,2510%
2017-2	0,6809%
2017-3	0,3243%
2017-4	0,3735%
2017-5	0,3282%
2017-6	0,2848%
2017-7	0,1810%
2017-8	0,9516%
2017-9	0,6143%
2017-10	0,4470%
2017-11	0,3436%
2017-12	0,3936%
2018-1	0,2263%
2018-2	0,6925%
2018-3	0,2870%
2018-4	0,3343%
2018-5	0,2759%
2018-6	0,2261%
2018-7	0,1592%
2018-8	0,7707%
2018-9	0,5566%
2018-10	0,4353%
2018-11	0,3981%
2018-12	0,3617%
2019-1	0,2238%
2019-2	0,6516%
2019-3	0,3092%
2019-4	0,2882%
2019-5	0,2594%
2019-6	0,2830%
2019-7	0,2395%
2019-8	1,0703%
2019-9	0,5112%
2019-10	0,4416%
2019-11	0,3462%
2019-12	0,4767%
2020-1	0,2069%
2020-2	0,6847%
2020-3	0,3627%
2020-4	0,3112%
2020-5	0,2864%	0.3556
2020-6	0,2960%
2020-7	0,1576%
2020-8	0,7854%
2020-9	0,4825%
2020-10	1,1194%
2020-11	3,0394%
2020-12	4,1813%
2021-1	6,5877%
2021-2	2,3346%
2021-3	0,2693%
2021-4	0,2578%	0.3554
2021-5	0,2067%	0.3431

2021-5 = 0.3431 = Median of all period 5 of current and previous years

2021-4=0.3554= Median of all period 4 of current and previous years

2020-5= 0.3556 = Median of all period 5 of current and previous years

I fear I might have to make this a script thing and precalculate my medians...

[rubenmarin]

Hi, yes, I think you will need some script help to precalculate, but before doing that, can you confirm you are calculateing the median?

For median you need to sort values and select the one in middle, if elements are even you will have 2 middle number and the value to return is tghe average between those two.

So for 2020  middle values for period 5 are 0,3373 and 0,368, and the average is 0,35265. And this value is not the one is shown on your example. How is this median calculated.

Anyway, I think you can do a sorted load of the values, spliting Year_month values in two fields, using order by to sort by month and year and using Peek() to access previous records. One field can count the accumulated records you have readed like: If(Year=Peek(Year), Peek(CountedRecords)+1, 1) as CountedRecords.

Use CountedRecords value to know if its Even or not and know how many records above you need to go with Peek() to retrieve the needed value.

https://help.qlik.com/es-ES/sense/September2020/Subsystems/Hub/Content/Sense_Hub/Scripting/InterReco...

[sibrulotte · Accepted solution]

Hi Ruben, very helpfull intervention. 

I'll assure you this is a median, and the middle values for 2020, period 5 is 0.3431 (2016) and 0.368 (2011). That's how I got 0.3556.

It's a solution I am not confortable scripting as I don't fully understand it. 

However, I managed to finish the job in excel within 30 seconds with an array formula {=Median(if(A1:A126 = A126;B1:B126))}.

And like that, power tools and excel took another bit out of Qlik. 😞