Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sibrulotte
Creator III
Creator III

Median of previous years for the same period

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_monthRatioMedian
2011-10,3539% 
2011-20,6369% 
2011-30,3922% 
2011-40,3874% 
2011-50,3680% 
2011-60,4135% 
2011-70,2552% 
2011-80,9637% 
2011-90,8341% 
2011-100,7181% 
2011-110,4170% 
2011-120,4996% 
2012-10,3793% 
2012-20,5727% 
2012-30,6026% 
2012-40,5538% 
2012-50,5367% 
2012-60,5295% 
2012-70,6221% 
2012-81,5954% 
2012-90,7348% 
2012-100,5460% 
2012-110,5014% 
2012-120,5048% 
2013-10,4596% 
2013-20,6201% 
2013-30,3640% 
2013-40,3373% 
2013-50,4443% 
2013-60,3363% 
2013-70,2252% 
2013-80,8969% 
2013-90,7087% 
2013-100,5169% 
2013-110,6330% 
2013-120,7937% 
2014-10,3207% 
2014-20,6478% 
2014-30,4240% 
2014-40,3755% 
2014-50,6258% 
2014-60,5725% 
2014-70,3483% 
2014-81,5641% 
2014-91,0120% 
2014-100,6037% 
2014-110,6718% 
2014-120,5467% 
2015-10,3316% 
2015-20,7538% 
2015-30,4773% 
2015-40,4345% 
2015-50,3700% 
2015-60,2843% 
2015-70,2127% 
2015-81,0427% 
2015-90,8133% 
2015-100,6278% 
2015-110,5514% 
2015-120,3961% 
2016-10,3440% 
2016-20,7844% 
2016-30,3963% 
2016-40,4265% 
2016-50,3431% 
2016-60,3176% 
2016-70,2605% 
2016-81,1486% 
2016-90,7494% 
2016-100,5326% 
2016-110,4334% 
2016-120,4010% 
2017-10,2510% 
2017-20,6809% 
2017-30,3243% 
2017-40,3735% 
2017-50,3282% 
2017-60,2848% 
2017-70,1810% 
2017-80,9516% 
2017-90,6143% 
2017-100,4470% 
2017-110,3436% 
2017-120,3936% 
2018-10,2263% 
2018-20,6925% 
2018-30,2870% 
2018-40,3343% 
2018-50,2759% 
2018-60,2261% 
2018-70,1592% 
2018-80,7707% 
2018-90,5566% 
2018-100,4353% 
2018-110,3981% 
2018-120,3617% 
2019-10,2238% 
2019-20,6516% 
2019-30,3092% 
2019-40,2882% 
2019-50,2594% 
2019-60,2830% 
2019-70,2395% 
2019-81,0703% 
2019-90,5112% 
2019-100,4416% 
2019-110,3462% 
2019-120,4767% 
2020-10,2069% 
2020-20,6847% 
2020-30,3627% 
2020-40,3112% 
2020-50,2864%0.3556
2020-60,2960% 
2020-70,1576% 
2020-80,7854% 
2020-90,4825% 
2020-101,1194% 
2020-113,0394% 
2020-124,1813% 
2021-16,5877% 
2021-22,3346% 
2021-30,2693% 
2021-40,2578%0.3554
2021-50,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...

1 Solution

Accepted Solutions
sibrulotte
Creator III
Creator III
Author

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. 😞

View solution in original post

2 Replies
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
Creator III
Creator III
Author

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. 😞