Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
My bad, there isn't a function like RangeMedian ... you can use RangeFractile instead
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)
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.
My bad, there isn't a function like RangeMedian ... you can use RangeFractile instead
Thank you Sunny, it solved a big part of the problem.