Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
See this document for a solution: Calculating rolling n-period totals, averages or other aggregations
Do you have week (2015-16) as dimension?
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
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))
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.
Can you please post the same app to work on the same?
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.