topic Re: How to Calculate cumulative value in QlikView

How to Calculate cumulative value

venkatbza — Sat, 16 Nov 2024 02:50:22 GMT

Hi Friends,

I am trying to convert below excel logic's to Qlikview script level but I am not able to find the way please help me.

=SUMIF(A3:A8,B3)

My Data is below, I have 'value' and 'reference value' as columns now I need to create a Column called 'Cumulative Value'. Cumulative Value column condition is sum of Value if reference value is greater then or equal to(=SUMIF(A3:A8,B3)).

DATA:

Value	Reference value
10	>=11
20	>=21
30	>=35
40	>=41
50	>=55
60	>=60

OUT required:

		OUTPUT Column
Value	Reference value	Cumulative value
10	>=12	200	EX:-Output column should show the sum of "value" what ever the value is >=12
20	>=21	180	EX:-Output column should show the sum of "value" what ever the value is >=21
30	>=35	150	EX:-Output column should show the sum of "value" what ever the value is >=35
40	>=41	110	EX:-Output column should show the sum of "value" what ever the value is >=41
50	>=55	60	EX:-Output column should show the sum of "value" what ever the value is >=55
60	>=60	60	EX:-Output column should show the sum of "value" what ever the value is >=60

Thanks,

Venkat

Re: How to Calculate cumulative value

sunny_talwar — Thu, 01 Aug 2019 19:06:29 GMT

Here is one option... but may be not the best out there...

Table: LOAD Value, [Reference value], Num#(Replace([Reference value], '>=', '')) as [Reference value Tmp]; LOAD * INLINE [ Value, Reference value 10, >=11 20, >=21 30, >=35 40, >=41 50, >=55 60, >=60 ]; Join (Table) LOAD Value as TestValue Resident Table; FinalTable: LOAD Value, [Reference value], Sum(If(TestValue >= [Reference value Tmp], TestValue)) as Output Resident Table Group By Value, [Reference value]; DROP Table Table;

Re: How to Calculate cumulative value

venkatbza — Fri, 02 Aug 2019 08:42:21 GMT

Hi Sunny,

Thanks for your reply, it is working fine but Join condition is creating performance issue, it took hours to reload. Instead of join I have tried with concatenation but showing wrong values. is there any other way that I can use instead of JOIN condition?

Thanks

Re: How to Calculate cumulative value

sunny_talwar — Fri, 02 Aug 2019 16:43:13 GMT

All ways would run into the same issue....

Re: How to Calculate cumulative value

Brett_Bleess — Thu, 22 Aug 2019 20:52:51 GMT

Venkat, have a look at the following post, not sure if it will help or not, but I figured it might be worth a shot.

https://community.qlik.com/t5/Qlik-Design-Blog/Full-Accumulation-example/ba-p/1475445

Regards,
Brett