Median of 6 weeks rolling

[nictheswede]

This is my first post and i´m a beginner så bear with me

I´m using the following code to calculate the sum of sales for the 6 weeks prior to the selected week but i need to know the median of the sales, how can I use the code to accomplish that? (I´m also not sure weather to use "only(Week) or min(Week) and max(Week)").

sum({<Week={">=$(=only(Week)-6)<=$(=only(Week)-1)"}>}Sales)

[Gysbert_Wassenaar]

Use the median function instead of the sum function if you want to calculate the median amount.

[nictheswede]

Ok, I´ve tried to change sum for median but it won´t work the way it suppose to, is there somthing I´m missing?

I have the following values:

Week	Sales
4	227
5	89
6	45
7	45
8	185
9	89

If I change sum for median and select week 10, I get 22 but the right answer should be 89.

median({<Week={">=$(=only(Week)-6)<=$(=only(Week)-1)"}>}Sales)

[vikasdesai]

Hello Friend

Please use Following Expression,

Median({<DateField={">=$(=Weekstart(max(DateField),-5))<=$(=max(DateField))"},Year=,Month=>} Sales)

Regards

Vikas

[sunny_talwar]

Or may be this where you sum each week and then find the median from those weeks

Median(Aggr(Sum({<Week={">=$(=only(Week)-6)<=$(=only(Week)-1)"}>}Sales), Week))

[sunny_talwar]

Try this may be

Median(Aggr(Sum({<Week={">=$(=only(Week)-6)<=$(=only(Week)-1)"}>}Sales), Week))

[Gysbert_Wassenaar]

Sounds like you don't want the median of the sales, but the median of the last six weekly sums of sales. It that's what you want then try stalwar1‌‌ expression

[nictheswede · Accepted solution]

Thanks for all your input!

I used your expression, Sunny, but I added the set in median aswell, otherwise it wouldn´t work.

Median({<Week={">=$(=min(Week)-6)<=$(=max(Week)-1)"}>}Aggr(Sum({<Week={">=$(=min(Week)-6)<=$(=max(Week)-1)"}>}Sales), Week))

[sunny_talwar]

Good decision....