Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

How do I draw a Cumulative % line Chart on Qliksense

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 ScoreJan-20Feb-20Mar-20Apr-20May-20Jun-20Jul-20Aug-20Sep-20Oct-20Nov-20Dec-20Jan-21Feb-21Mar-21Apr-21May-21May-22
Met114198176168705944140344245416363823500
Not Met44954853352410107510012021
Totals1582932111971058354150414746416364843521

Chart should ideally look like this:
(With anything past Apr 20 be projected looking)
I've attached my sample data below in an excel.

anything after April 20 is forward lookinganything after April 20 is forward looking

 Cumulative score below:

Cumulative ScoreJan-20Feb-20Mar-20Apr-20May-20Jun-20Jul-20Aug-20Sep-20Oct-20Nov-20Dec-20Jan-21Feb-21Mar-21Apr-21May-21May-22
Cumulative Met1143124886567267858299691003104510901131119412571339137413741374
Cumulative Not Met44139187240275299309319326331332332332333335335337338
Cumulative Total1584516628599641047110112511292133913851426148915531637167216741675
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:
TRO Wrong.PNG

1 Solution

Accepted Solutions
Highlighted
Specialist II
Specialist II

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

View solution in original post

2 Replies
Highlighted
Specialist II
Specialist II

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

View solution in original post

Highlighted
Contributor III
Contributor III

oh thanks for the identification 

have made the amendments all is good now.