Need some help calculating an average

[chrisjones16]

Hi All,

Hoping someone could help me out with a calculation im trying to build. I have attached a quick snapshot of dummy data to help explain this better.

Below is the granular data (Item 1.), what im trying to do is build a 6 month rolling metric. The way this needs to be calculated is by adding up the last 6 months pass rate by each month (e.g. Item 2. see below)  and then dividing by six.

So for example if the user selects June , i need to calculate the pass rate the by last six months on a monthly basis (Jun2019, May2019, Apr2019 etc)  then sum the monthly pass rates and divide by 6.  (item 3.)

(pass rate = students passed/ no of students)

Any help would be greatly appreciated.

Item 1.

Row_date	No Of Students	Students passed
04/11/2018	2	1
07/11/2018	4	3
23/11/2018	2	1
04/12/2018	7	6
12/12/2018	8	7
15/12/2018	4	4
19/12/2018	6	5
21/12/2018	2	1
01/01/2019	4	3
04/01/2019	1	1
11/01/2019	9	9
01/02/2019	2	1
11/02/2019	4	3
20/02/2019	2	1
02/03/2019	7	6
19/03/2019	6	5
05/04/2019	4	3
10/04/2019	6	5
20/04/2019	5	5
11/05/2019	4	3
18/05/2019	4	3
21/05/2019	9	8
11/06/2019	6	6
17/06/2019	7	5

Item 2.

Month	pass rate
Dec-18	95
Jan-19	92
Feb-19	90
Mar-19	97
Apr-19	92
May-19	92
Jun-19	90

Item 3. 

Final output needed

	rolling 6 month pass rate
Dec-18
Jan-19
Feb-19
Mar-19
Apr-19
May-19	96% (sum( Dec18 to May19)/6)
Jun-19	92 % (sum( Jan19 to Jun19)/6)

[JordyWegman]

Hi Chris,

Some questions before the solution..

1. Do you have item two already prepared? Or does it start at one?
2. Can we prepare item three in the script? This would be a lot easier

Jordy

Climber

[chrisjones16]

Hi , thanks for getting back. No it starts with item1 but could prepare it in the script. Not sure how i would approach that.

[sunny_talwar]

Not sure how you are getting your numbers within Item 2... but may be see if this is what you want?

[chrisjones16]

Thanks Sunny,  yes the way you have shown is how i get the percentages in item2 . Will give  your range avg calculation a go which looks promising.

Could you help me understand why the    *Avg(1)  is needed?

[sunny_talwar]

I used that to make sure that if you select June, it only shows June. Without Avg(1), it will show all the Months because I am ignoring selection in Month in the inside aggregation to be able to calculate RangeAvg() over 6 months.

[chrisjones16]

Hi,

i implemented your solution and it works great but i want to take it further.  This works fine if i have all the months present(no date filtered). But if i filter by may2019 the data gets filtered by May and the calculation doesnt work as it only shows  the may pass rate not the previous 6 months(including may).

How can i get the result to include the previous 6 months when a users filters by a specific month.

this is a measure which im looking to add to a kpi object in qliksense .

Thanks