Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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 ];
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 ];
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!
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.