topic Re: Create cumulative line chart as percentage in QlikView

Create cumulative line chart as percentage

kstakpcn — Fri, 20 Nov 2020 18:58:42 GMT

Hello all,

Would it be possible to create a line chart as this?

The amount calculated is in % and that is as shown below in the formula a cummulative. For instance, in a given month, lets say 7 - July, the % is calculated as the total sum from january to july of value A devided by the same range of sum for value B.

Thank you very much in advance!

Re: Create cumulative line chart as percentage

Anil_Babu_Samineni — Mon, 16 Nov 2020 17:45:26 GMT

Yes, For me it is possible since you are using Qlikview. Please share some sample data to work

Re: Create cumulative line chart as percentage

kstakpcn — Mon, 16 Nov 2020 17:55:29 GMT

Thanks for the reply. In what format can I share data? excel? Would that be ok?

Re: Create cumulative line chart as percentage

Anil_Babu_Samineni — Mon, 16 Nov 2020 17:56:44 GMT

That works otherwise, Share the QVW with excel file to save time

Re: Create cumulative line chart as percentage

kstakpcn — Mon, 16 Nov 2020 19:02:19 GMT

There please find attached the excel file with the demo data. Thank you so much!

Re: Create cumulative line chart as percentage

edwin — Mon, 16 Nov 2020 21:41:37 GMT

try this:

=rangesum(above(B_Amount,0,RowNo()))/rangesum(above(Gross_Amount,0,RowNo()))

make sure you sort by month

Re: Create cumulative line chart as percentage

kstakpcn — Tue, 17 Nov 2020 13:43:14 GMT

Thank you that was really helpful,

To make it more challenging, would it be however possible to consider the rolling effect from January and on and leave December 2019 alone as is?

The offset variable doesn't produce me the correct results if i were to set it as 1 or 2.

Attached is the Excel with the formula and the december 2019 added in it.