Calculating Cumulative Monthly Average with Denominator based on the latest month of the year

[leonchoo]

Hi there, I am new to Qlikview and I am trying to create a cumulative line graph which shows the ratio of Sales to Current Stuff

Here is an example of my data:

Year	Month	Sales	Staff
2017	Jan	1	3
2017	Feb	2	5
2017	Mar	4	5
2017	Apr	5	3
2017	May	6	6
2017	Jun	2	3
2017	Jul	3	5
2017	Aug	4	2
2017	Sep	6	6
2017	Oct	4	4
2017	Nov	3	6
2017	Dec	5	5
2018	Jan	1	4
2018	Feb	3	2
2018	Mar	4	8
2018	Apr	2	4
2018	May	5	6
2018	Jun	1	2
2018	Jul	6	5
2018	Aug	7	3
2018	Sep	3	7
2018	Oct	4	5
2018	Nov	5	3
2018	Dec	2	5

I would like to calculate the ratio based on the latest month of the year as a point on the line graphs for 2017 and 2018  = Sales/(Staff Count for the latest month)

E.g. for Jan 2017, ratio would be 1/3

        for Feb 2017, ratio would be (1+2)/5

       for Jan 2018, ratio would be 1/4

       for Jan 2018, ratio would be (1+3)/2

where the denominator is based on the staff count for the latest month.

It would mean a lot if someone could provide me with an expression on this. Thank you!

[sunny_talwar · Accepted solution]

Try this expression

=RangeSum(Above(Sum(Sales), 0, MonthNum))/Sum(Staff)

Where MonthNum and MonthYear are created in the script

Table:
LOAD *,
	 Num(Month(Date#(Month, 'MMM'))) as MonthNum,
	 Date(Date#(Year&Month, 'YYYYMMM'), 'MMM-YYYY') as MonthYear;
LOAD * INLINE [
    Year, Month, Sales, Staff
    2017, Jan, 1, 3
    2017, Feb, 2, 5
    2017, Mar, 4, 5
    2017, Apr, 5, 3
    2017, May, 6, 6
    2017, Jun, 2, 3
    2017, Jul, 3, 5
    2017, Aug, 4, 2
    2017, Sep, 6, 6
    2017, Oct, 4, 4
    2017, Nov, 3, 6
    2017, Dec, 5, 5
    2018, Jan, 1, 4
    2018, Feb, 3, 2
    2018, Mar, 4, 8
    2018, Apr, 2, 4
    2018, May, 5, 6
    2018, Jun, 1, 2
    2018, Jul, 6, 5
    2018, Aug, 7, 3
    2018, Sep, 3, 7
    2018, Oct, 4, 5
    2018, Nov, 5, 3
    2018, Dec, 2, 5
];

[leonchoo]

Thank you Sunny! But one problem that I have, is that the number of staff is counted based on another field.

Where staff for that month= count({<Employment_Status = 'active'>}, distinct Employee_Number)

Could you suggest how I can sum(staff) = sum(count({<Employment_Status = 'active'>}, distinct Employee_Number)) ?

with greatest thanks!

[sunny_talwar]

Based on the sample provided above, what are you hoping to see as your output? It would be great if you are able to provide this information in an Excel file.