How to Calculate cumulative value

[venkatbza]

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

[sunny_talwar]

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;

[venkatbza]

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

[sunny_talwar]

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

[Brett_Bleess]

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