Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for
Did you mean:
Partner - Contributor III

## Cumulative sum with several measures

Hi everyone,

I'm learing how to use QlikSense, and have used a lot of what I've found here : thank you to everyone in this community.

I'm currently facing an issue and can't seem to find a solution to my problem.

Context :

My main measure is named "Amount". It roughly represents budget.

The amount is linked to a date in which the amount is supposed to be spent.
There are several categories to the amount : Spent, Planned, To be planned, Planned for risks.

What I need to do :

I am trying to make a bar graph with cumulated values of the amount, split by each category.

What I've tried :
I have been using the following formula for each type of amount :

Sum(Aggr( Rangesum(Above(Sum({\$<Year=,"Amount_type"={'Spent'}>}Amount) ,0,\$(=Count(Distinct {1} Year)))), (Year, (Numeric, Ascending))))

See below

My issue :

As you can see, is that the light green (Réalisé = 'spent') for example does not carry in 2020 and above.
While I don't have any 'spent' amount in 2020 and above, I don't understand why it does not keep the 2019 value, and what I need to do to keep it.

Thanks for your help,

Alexis

Labels (2)

• ### Set Analysis

1 Solution

Accepted Solutions
MVP

Isn't this the goal?

I changed the expression for 'Cumulated_Done' to this

```If(GetSelectedCount([Year])=1,
Sum(Aggr(RangeSum(Above(Sum({\$<YearMonth, [Amount Type] = {'Done'}>} [Amount]), 0, \$(=Count(Distinct {1} Year)*12))) + Sum(0), (YearMonth, (LOAD_ORDER, Ascending)))),
Sum(Aggr(RangeSum(Above(Sum({\$<Year, [Amount Type] = {'Done'}>} [Amount]), 0, \$(=Count(Distinct {1} Year)))) + Sum(0), (Year, (Numeric, Ascending))))
)```
9 Replies
MVP

Can you try this and see if this works

```Sum(Aggr(
Rangesum(Above(
Sum({\$<Year, "Amount_type" = {'Spent'}>} Amount) + Sum(0)
, 0, \$(=Count(Distinct {1} Year))))
, (Year, (Numeric, Ascending))))```
Partner - Contributor III
Author

Hello,

I have tried, but it has had no effect on my graph.
What was it supposed to make ?

MVP

I hoped it would start showing the missing bar for Réalisé. This could be an issue related to the missing data, but difficult to say without having to look. Would you be able to share a sample?

Partner - Contributor III
Author

Hello,

Sorry for the late reply.

Here you can find somme dumy data :

• Date file
• Amount file
• According qvf

I have created several master elements :

• master dimension date : Year / YearMonth
• Master measures :
• my 4 sum amounts with Amount types
• my 4 cumulated values (there is an if statement to keep a cumulated value on the year if I have only one selected)

I hope this helps

Thanks a lot for your help

MVP

Isn't this the goal?

I changed the expression for 'Cumulated_Done' to this

```If(GetSelectedCount([Year])=1,
Sum(Aggr(RangeSum(Above(Sum({\$<YearMonth, [Amount Type] = {'Done'}>} [Amount]), 0, \$(=Count(Distinct {1} Year)*12))) + Sum(0), (YearMonth, (LOAD_ORDER, Ascending)))),
Sum(Aggr(RangeSum(Above(Sum({\$<Year, [Amount Type] = {'Done'}>} [Amount]), 0, \$(=Count(Distinct {1} Year)))) + Sum(0), (Year, (Numeric, Ascending))))
)```
MVP

Updated all the expression because I saw that even 'Cumulated_Commitment' was missing for 2023

Partner - Contributor III
Author

Amazing, thank you very much !

Could you please share how it works ? I'm not sure to understand

MVP

Since Amount Type = 'Done' was non existent for the years 2020 onwards, the expression was treated as null and null cannot be accumulated with a number. Since we knew that there are other Amount Types which are not null for the other period, I added Sum(0) which will change the expression from null to 0. Now 0 can be accumulated with other numbers.

Does that make sense?

Partner - Contributor III
Author

Yes that makes perfect sense, thank you !

I find that it is a very non-intuitive way of implementing it, but that is another story 🙂