Cumulative field by two dimensions

[lomi89]

Hi team,

I would like to get the field "Cumulative cost"

year	contract	code claim	cost	Cumulative cost
1	a	1	10,00 €	10,00 €
1	a	2	20,00 €	30,00 €
2	a	3	5,00 €	5,00 €
2	a	4	30,00 €	35,00 €
3	b	5	40,00 €	40,00 €
3	b	6	5,00 €	45,00 €
3	b	7	5,00 €	50,00 €

Do you have some idea? I would like to have it inside of the script.

Thank you very much,

[edwin]

there are a lot of implementations of cumulative sums here is one example that ignores the first record:

https://community.qlik.com/t5/QlikView-App-Dev/Cumulative-SUM-in-Qlik-Expression/td-p/1399466

this is most interesting as it gives you an idea how to tweak the rangesum function

[PrashantSangle]

In front end use below expression

RangeSum(Above(Aggr(Sum(cost),year,contract,code),0,RowNo()))

Regards,

Prashant Sangle

[lomi89]

Could be possible to do it in the editor platform (back)?

Thank you

[edwin]

if you mean script editor, i would not advise it as your cumulative field will not be dynamic when user starts filtering.

[PrashantSangle · Accepted solution]

Hi @lomi89 ,

for back end try below script.

Base:
Load
year,
contract,
Text(code) as code,
claim_cost
Inline[
year, contract, code, claim_cost
1, a, 1, 1000
1, a, 2, 2000
2, a, 3, 500
2, a, 4, 3000
3, b, 5, 4000
3, b, 6, 500
3, b, 7, 500
];

NoConcatenate
Final:
Load year as yr,
contract as cnt,
code as cde,
if(previous(year&'_'&contract)=year&'_'&contract,claim_cost+peek(cum_cost),claim_cost) as cum_cost
Resident Base
order by year,contract
;

Regards,

Prashant Sangle

[PrashantSangle]

Final table is your required output