topic Re: Show only selected week median in QlikView

Show only selected week median

nictheswede — Wed, 25 Nov 2020 16:16:04 GMT

Hi all

I use the following expression to calculate the median volume for 6 weeks prior to the selected week.

median({<Week={">=$(=min(Week)-6)<=$(=max(Week)-1)"}>}Aggr(Sum({<Week={">=$(=min(Week)-6)<=$(=max(Week)-1)"}>} Volume), Store, Article, Week))

It works as it soppose to for now, as seen below:

Store	Article	Volume median	Volume current week
332	10546	11	13

I now want to add a dimension "week" but the I get the follwong result, I dont want to display all the weeks that the median is calculated on, only the row with the selected week (selected 16).

Store	Article	Week	Volume median	Volume current week
332	10546	10	6,0	0
332	10546	11	10,0	0
332	10546	12	16,0	0
332	10546	13	12,0	0
332	10546	14	14,0	0
332	10546	15	8,0	0
332	10546	16	-	13

I also want to select more than 1 week at the same time, but the I get this result (selected 15, 16):

Store	Article	Week	Volume median	Volume current
332	10546	9	18	0
332	10546	10	6	0
332	10546	11	10	0
332	10546	12	16	0
332	10546	13	12	0
332	10546	14	14	0
332	10546	15	8	8
332	10546	16	-	13

Has anyone got any ideas how I can solve it?

//Christian

Re: Show only selected week median

sunny_talwar — Thu, 26 Apr 2018 11:24:17 GMT

I think what you might need here is a RangeMedian function with Above() function... something like this

RangeMedian(Above(Sum({<Week>} Volume), 0, RowNo())) * Avg(1)

Re: Show only selected week median

nictheswede — Mon, 30 Apr 2018 09:43:50 GMT

Thanks for you answer but I'm not familiar with the RangeMedian function and I don't seem to find any information about it anywhere.

Re: Show only selected week median

sunny_talwar — Mon, 30 Apr 2018 10:53:07 GMT

My bad, there isn't a function like RangeMedian ... you can use RangeFractile instead

RangeFractile - script and chart function

Re: Show only selected week median

nictheswede — Tue, 08 May 2018 07:37:41 GMT

Thank you Sunny, it solved a big part of the problem.