Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
andrewcusack
New Contributor II

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
MVP & Luminary
MVP & Luminary

Re: Rolling Day of Week Average

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
andrewcusack
New Contributor II

Re: Rolling Day of Week Average

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?