Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Partner
Partner

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

Capture.PNG

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 (3)
1 Solution

Accepted Solutions

Re: Cumulative sum with several measures

Isn't this the goal?

image.png

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

Re: Cumulative sum with several measures

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
Partner

Re: Cumulative sum with several measures

Hello, 

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

 

Re: Cumulative sum with several measures

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
Partner

Re: Cumulative sum with several measures

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

 

Re: Cumulative sum with several measures

Isn't this the goal?

image.png

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))))
)

Re: Cumulative sum with several measures

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

image.png

Partner
Partner

Re: Cumulative sum with several measures

Amazing, thank you very much !

 

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

Re: Cumulative sum with several measures

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
Partner

Re: Cumulative sum with several measures

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 🙂