Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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. 😞
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.
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. 😞