topic Re: Rolling 20/52 weeks in QlikView

Rolling 20/52 weeks

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I would like to produce 2 seperate line charts showing me a moving average. My current expression shows me the averages per week but not a moving average as in the example below:

Week 2016-08 would be an average of the last 20 weeks:

2016-8

2016-7.....

.... 2015-40

Current expression:

=sum({<Week=, MonthName=, Year=, DivisionName = {'***'} + {'***'}, PerformanceDate ={">=$(=date(Today()-140))<=$(=date(Today()))"}>} Production.DrivingGrossDurationHrs)

Count({<Week=, MonthName=, Year=, DivisionName = {'***'} + {'***'}, PerformanceDate ={">=$(=date(Today()-140))<=$(=date(Today()))"}>} Production.DriverName)*5

2nd chart would be showing me a 52 weeks moving average in stead of the 20 moving average.

Hope someone can help me 😃

Kind regards

Re: Rolling 20/52 weeks

Gysbert_Wassenaar — Fri, 11 Mar 2016 09:18:57 GMT

See this document for a solution: Calculating rolling n-period totals, averages or other aggregations

Re: Rolling 20/52 weeks

Kushal_Chawda — Fri, 11 Mar 2016 09:21:03 GMT

Do you have week (2015-16) as dimension?

Re: Rolling 20/52 weeks

Fri, 11 Mar 2016 09:36:16 GMT

I have:

Year

Week

Month

Not a table for

2015-1

2015-2

...

2016-1

2016-2

But you can combine the dimensions year & week

Re: Rolling 20/52 weeks

Kushal_Chawda — Fri, 11 Mar 2016 09:45:35 GMT

try creating Weekname in script like below

weekname(Date) as WeekName.

Create a line chart

Dimension:

WeekName

Expression:

sum(aggr(rangesum(above(total sum({<Week=, MonthName=, Year=,WeekName=, DivisionName = {'***'} + {'***'}>} Production.DrivingGrossDurationHrs),0,22)),WeekName)) /

sum(aggr(rangesum(above(total Count({<Week=, MonthName=, Year=,MonthName=, DivisionName = {'***'} + {'***'}>} Production.DriverName)*5,0,22)),WeekName))

Re: Rolling 20/52 weeks

Fri, 11 Mar 2016 11:18:01 GMT

Hi,

Able to produce the Weekname however the expression does not add up the averages of the formula of the past 20 weeks.

So even when I select the weeks it does not give me a rolling period of 20 weeks. If I take the formula apart and use weekname it does give the correct values regarding production hours and drivers.

Re: Rolling 20/52 weeks

Kushal_Chawda — Fri, 11 Mar 2016 11:23:44 GMT

Can you please post the same app to work on the same?

Re: Rolling 20/52 weeks

Wed, 23 Mar 2016 08:35:23 GMT

Hi unfortunately my boss does not approve app posting.

However the formula seems the work with the following expression:

(rangesum(above(Total sum({< Division = {'***'} + {'****'}>} Production.DrivingGrossDurationHrs),0,20)))/

rangesum(above(Count({< Division = {'****'} + {'****'}>} Driver),0,20))*5

However when switched to a line chart instead of a pivot table it gives me different results. Being fairly new to qlikview could you explain me what happens? Assuming the problem occurs in the expression.