2 Replies Latest reply: Feb 20, 2013 4:51 AM by Andrew Cusack RSS

    Rolling Day of Week Average

    Andrew Cusack

      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

        • Re: Rolling Day of Week Average
          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.

            • Re: Rolling Day of Week Average
              Andrew Cusack

              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?