Calculating A Rolling 6 month average

[mgajja2019]

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:

MonthYear	No of students	No students passed	Pass Rate	Six month rate
Jun-2018	823	794	96.32%
Jul-2018	697	671	96.20%
Aug-2018	752	723	96.03%
Sep-2018	727	701	96.30%
Oct-2018	817	778	95.12%
Nov-2018	762	744	97.58%	16.26%
Dec-2018	756	729	96.36%	16.06%
Jan-2019	779	747	95.90%	15.98%
Feb-2019	717	685	95.49%	15.91%
Mar-2019	796	754	94.62%
Apr-2019	854	794	93.01%
May-2019	946	892	94.16%
Jun-2019	27	27	100.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.

[Sue_Macaluso]

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