Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Rolling Day of Week Average

Hi QlikTechies...I'm really hoping one of you very clever people out there can help me solve this problem, please!

I need to replicate the table below.   The problem column is "Last 4 Wk Ton Avg".   This is the average, for the last 4 weeks, for that Day of Week.

Capture.PNG

I've been trying to achive this with a variety of expressions, like rangeavg and aggr, but without success so far.   I know I can create the average quite easily in the script, but I'd like (if possible), to keep the calculation dymanic so the user can make selections and the average will update.

Any advise would be greatly appreciated.

Cheers

Andrew

2 Replies
Gysbert_Wassenaar

Try something like this:

rangeavg(

     sum(Tons),

     above(sum(Tons),7,1),

     above(sum(Tons),14,1),

     above(sum(Tons),21,1))

Should work as long as you have no gaps in your Dates field.


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

Hi Gysbert,

Thanks for the advise, it did work once i had created a single dimension with the Date and Day of Week concatinated and then a sort expression to order correctly by Date.

The next step is making sure the Avg is always populated and always for the last 4 weeks.   I understand why the Avg doesn't appear for 7 days (you can see the expression I've used below) and I understand why the Avg isn't actually a 4 weekly average until the 29th day (before the 29th Jan it's a 3 week average, before the 22nd a 2 week average etc)...

Capture.PNG

rangeavg(

     above(sum( aggr(AWB_Flight.weight, flight_awb_seq) )/1000,7,1),

     above(sum( aggr(AWB_Flight.weight, flight_awb_seq) )/1000,14,1),

     above(sum( aggr(AWB_Flight.weight, flight_awb_seq) )/1000,21,1),

     above(sum( aggr(AWB_Flight.weight, flight_awb_seq) )/1000,28,1))

I just to modify the expression to ignore date selections but the above function still limits my average depending on the number of records above it.

Any ideas how to solve this please?