Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qliksense expert and fellow Qlik Developers
I'm met with a problem, I can't seem to create a cumulative % line chart without it going pass 100%.
I want create a cumulative % line on a line chart to display my cumulative TRO met % over a 12 months and more (forward looking). I do have the concept of the chart out on excel and have attached the relative information below.
TRO Score | Jan-20 | Feb-20 | Mar-20 | Apr-20 | May-20 | Jun-20 | Jul-20 | Aug-20 | Sep-20 | Oct-20 | Nov-20 | Dec-20 | Jan-21 | Feb-21 | Mar-21 | Apr-21 | May-21 | May-22 |
Met | 114 | 198 | 176 | 168 | 70 | 59 | 44 | 140 | 34 | 42 | 45 | 41 | 63 | 63 | 82 | 35 | 0 | 0 |
Not Met | 44 | 95 | 48 | 53 | 35 | 24 | 10 | 10 | 7 | 5 | 1 | 0 | 0 | 1 | 2 | 0 | 2 | 1 |
Totals | 158 | 293 | 211 | 197 | 105 | 83 | 54 | 150 | 41 | 47 | 46 | 41 | 63 | 64 | 84 | 35 | 2 | 1 |
Chart should ideally look like this:
(With anything past Apr 20 be projected looking)
I've attached my sample data below in an excel.
Cumulative score below:
Cumulative Score | Jan-20 | Feb-20 | Mar-20 | Apr-20 | May-20 | Jun-20 | Jul-20 | Aug-20 | Sep-20 | Oct-20 | Nov-20 | Dec-20 | Jan-21 | Feb-21 | Mar-21 | Apr-21 | May-21 | May-22 |
Cumulative Met | 114 | 312 | 488 | 656 | 726 | 785 | 829 | 969 | 1003 | 1045 | 1090 | 1131 | 1194 | 1257 | 1339 | 1374 | 1374 | 1374 |
Cumulative Not Met | 44 | 139 | 187 | 240 | 275 | 299 | 309 | 319 | 326 | 331 | 332 | 332 | 332 | 333 | 335 | 335 | 337 | 338 |
Cumulative Total | 158 | 451 | 662 | 859 | 964 | 1047 | 1101 | 1251 | 1292 | 1339 | 1385 | 1426 | 1489 | 1553 | 1637 | 1672 | 1674 | 1675 |
TRO (Cumulative %) | 72% | 69% | 74% | 76% | 75% | 75% | 75% | 77% | 78% | 78% | 79% | 79% | 80% | 81% | 82% | 82% | 82% | 82% |
I've tried using
rangesum(above(count({<[TRO Forecast Date]={'>=01/01/2020'},[TRO Forecast Score]={'Met'}>}[Problem ID*+])),0,RowNo())
/rangesum(above(count( {<[TRO Forecast Date]={'>=01/01/2020'},[TRO Forecast Score]>} [Problem ID*+])),0,RowNo())
But the chart is not quite what i'm looking for:
Try this:
rangesum(above(count({<[TRO Forecast Date]={">=01/01/2020"},[TRO Forecast Score]={'Met'}>}[Problem ID*+]),0,RowNo()))
/rangesum(above(count( {<[TRO Forecast Date]={">=01/01/2020"},[TRO Forecast Score]>} [Problem ID*+]),0,RowNo()))
you a couple of issues with your formula: two of the brackets where in the wrong place; search in set analysis is done with double quotes (also make sure the dates in your table are in the same format otherwise the >= is not going to work)
ultimately the numbers in the excel file are incorrect: for example, for Mar-20 the total is 224 (176+48) and not 211 as in the spreadsheet
Try this:
rangesum(above(count({<[TRO Forecast Date]={">=01/01/2020"},[TRO Forecast Score]={'Met'}>}[Problem ID*+]),0,RowNo()))
/rangesum(above(count( {<[TRO Forecast Date]={">=01/01/2020"},[TRO Forecast Score]>} [Problem ID*+]),0,RowNo()))
you a couple of issues with your formula: two of the brackets where in the wrong place; search in set analysis is done with double quotes (also make sure the dates in your table are in the same format otherwise the >= is not going to work)
ultimately the numbers in the excel file are incorrect: for example, for Mar-20 the total is 224 (176+48) and not 211 as in the spreadsheet
oh thanks for the identification
have made the amendments all is good now.