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

Discussion board where members can learn more about Qlik Sense App Development and Usage.

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- App Development
- :
- Re: Accumulative sum by 2 dimensions

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

Ilhui

Contributor II

2020-11-26
04:28 PM

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

Accumulative sum by 2 dimensions

I'm trying to get a cumulative month to month sum on a table which is grouped by customer and year.

The idea is to sum "Total" value each month and then subtract that accumulated amount from the "CommittedSpend" column, which is the same for each year.

In the example below, first year of committed spent was $612,000 and the actual spent was $123,892 so the calculation was $612,000-$123,892=487,107.

On the second month, the spent was $175,997 and the accumulated spent was $300,890(124,892+175,995). Then the remaining was the subtraction of $612,000-$300,890 giving a result of 311,109, and so on.

This calculation should end on the 12th month of each year and then start again on the next year(if applied) for each customer.

I was using the code below, but that works only when a select a customer from a filter but it stops working if no selection is made.

` [CommittedSpend]-RANGESUM(ABOVE(TOTAL SUM(Total),0,12))`

293 Views

1 Solution

Accepted Solutions

Nicole-Smith

MVP

2020-11-27
12:11 PM

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

The TOTAL keyword in the above() is telling Qlik to ignore all of your dimensions. Since you want the dimensions taken into account with the cumulative sum, you cannot use the TOTAL function. The following expression should work, but you will need to make sure your dimensions are ordered properly and the sorting is in the proper order:

rangesum(above(sum(Total), 0, RowNo()))

229 Views

2 Replies

Nicole-Smith

MVP

2020-11-27
12:11 PM

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

The TOTAL keyword in the above() is telling Qlik to ignore all of your dimensions. Since you want the dimensions taken into account with the cumulative sum, you cannot use the TOTAL function. The following expression should work, but you will need to make sure your dimensions are ordered properly and the sorting is in the proper order:

rangesum(above(sum(Total), 0, RowNo()))

230 Views

Ilhui

Contributor II

2020-11-27
12:13 PM

Author

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

This works properly.

Thank you Nicole!

220 Views