topic Re: How do I draw a Cumulative % line Chart on Qliksense in App Development

How do I draw a Cumulative % line Chart on Qliksense

Keitaru — Sat, 16 Nov 2024 18:44:22 GMT

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.

anything after April 20 is forward looking

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:

Re: How do I draw a Cumulative % line Chart on Qliksense

lorenzoconforti — Fri, 24 Apr 2020 09:33:59 GMT

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

Re: How do I draw a Cumulative % line Chart on Qliksense

Keitaru — Fri, 24 Apr 2020 10:58:15 GMT

oh thanks for the identification

have made the amendments all is good now.