Rolling 20/52 weeks

[#1]

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

[Gysbert_Wassenaar]

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

[Kushal_Chawda]

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

[#4]

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

[Kushal_Chawda]

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))

[#6]

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.

[Kushal_Chawda]

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

[#8]

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.