Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- Cumulative sum with several measures

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

alexish

Partner - Contributor III

2019-04-04
05:57 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

3,024 Views

1 Solution

Accepted Solutions

sunny_talwar

MVP

2019-04-16
07:36 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2,940 Views

9 Replies

sunny_talwar

MVP

2019-04-04
08:50 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

3,003 Views

alexish

Partner - Contributor III

2019-04-05
04:35 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hello,

I have tried, but it has had no effect on my graph.

What was it supposed to make ?

2,987 Views

sunny_talwar

MVP

2019-04-05
07:09 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

2,977 Views

alexish

Partner - Contributor III

2019-04-16
05:35 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2,946 Views

sunny_talwar

MVP

2019-04-16
07:36 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2,941 Views

sunny_talwar

MVP

2019-04-16
07:38 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2,938 Views

alexish

Partner - Contributor III

2019-04-16
07:41 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Amazing, thank you very much !

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

2,933 Views

sunny_talwar

MVP

2019-04-16
08:40 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

2,914 Views

alexish

Partner - Contributor III

2019-04-16
09:28 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 🙂

2,904 Views