Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
@mgajja2019 Are you using QlikView to Sense? I would like to move this into the correct product forum. Thank you.