Show only selected week median

[nictheswede]

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?

Br

//Christian

[sunny_talwar]

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)

[nictheswede]

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.

[sunny_talwar · Accepted solution]

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

RangeFractile - script and chart function

[nictheswede]

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