Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Cumulative field by two dimensions

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,

Labels (6)

• ### Variables

1 Solution

Accepted Solutions

Hi @lomi89 ,

for back end try below script.

Base:
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:
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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
6 Replies
Master

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

In front end use below expression

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

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Contributor III
Author

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

Thank you

Master

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

Hi @lomi89 ,

for back end try below script.

Base:
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:
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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Final table is your required output

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Tags
Community Browser