Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mgajja2019
New Contributor

Calculating A Rolling 6 month average

Hi All,

Need some help on this after a few failed attempts.  I am trying to develop a metric which calculates a monthly average and then sums the previous six months  for each current month.

this is my summarised data:

MonthYearNo of studentsNo students passedPass RateSix month rate
Jun-201882379496.32% 
Jul-201869767196.20% 
Aug-201875272396.03% 
Sep-201872770196.30% 
Oct-201881777895.12% 
Nov-201876274497.58%16.26%
Dec-201875672996.36%16.06%
Jan-201977974795.90%15.98%
Feb-201971768595.49%15.91%
Mar-201979675494.62% 
Apr-201985479493.01% 
May-201994689294.16% 
Jun-20192727100.00% 

 

No of students is calculated by

Count(Distinct STUDENT_ID)

No Of students passed calculated by

Count(Distinct{OUTCOME={"PASS"}>}STUDENT_ID)

So the column I am trying to create is the six monthly rate column which sums the pass rate percentage for the pervious six months.

I first need to calculate the monthly percentage and then add up the previous six months then divide by 6.

I was trying to implement something like this but got lost.

aggr(sum( COUNT(distinct{<MonthYearTxt=, numMonthYear= {"> $(=(MAX(numMonthYear)-6)) <=$(=MAX(numMonthYear))"},[OUTCOME]={'PASS'}>} Student_ID)/

COUNT(distinct{<MonthYearTxt=, numMonthYear= {"> $(=(MAX(numMonthYear)-6)) <= $(=MAX(numMonthYear))"}, REVIEW_ID)

 

Any help would be greatly appreciated.

 

1 Reply
Community Manager
Community Manager

Re: Calculating A Rolling 6 month average

@mgajja2019 Are you using QlikView to Sense? I would like to move this into the correct product forum. Thank you.

Sue Macaluso