topic Re: Maximum of Cumulative Sums in a dynamic date range in App Development

Maximum of Cumulative Sums in a dynamic date range

elijahabel — Wed, 27 Jun 2018 19:56:50 GMT

I have a problem where I need to be able to display the maximum cumulative sum within a selected date range.

Data would look like the following, except we would have multiple departments as well:

Date	Department	Daily Net
6/3/2018	A	50
6/4/2018	A	60
6/5/2018	A	10
6/6/2018	A	-50
6/7/2018	A	20

As such, the Cumulative sum values if this date range (6/3/2018-6/7/2018) were selected would be:

50,

110,

120,

70,

I need to be able to show in a table the below (but it would have multiple departments):

Department	Max Cumulative Sum
A	120
B	50

The Date range should be able to be changed dynamically, and the calculation would need to dynamically change depending on the selected range.

I have looked into the RangeSum and Before functions, but my table does not include the date column, so i don't think that would work. The other option would be set analysis, but I'm not sure how to make it cumulative since in the examples I have seen, the dates are hardcoded or a static variable.

Thank you for all help beforehand!

Re: Maximum of Cumulative Sums in a dynamic date range

jwjackso — Wed, 27 Jun 2018 21:19:39 GMT

Take a look at Cronstrom's "As-Of" table (The As-Of Table)

Re: Maximum of Cumulative Sums in a dynamic date range

BalaBhaskar_Qlik — Thu, 28 Jun 2018 17:02:48 GMT

May be these expressions:

RangeSum(Above(TOTAL Sum(Daily Net), 1, RowNo(TOTAL)))

RangeSum(Sum(Daily Net), Above( total Sum(Daily Net))).

Re: Maximum of Cumulative Sums in a dynamic date range

elijahabel — Thu, 28 Jun 2018 18:55:30 GMT

I did not end up using this solution, but thank you! The reference the article made to Aggr tables made me realize that I didn't fully understand how Aggr worked. After learning that, i was able to solve with Range Sum

I used the below:

Max(Aggr(RangeSum(Above(Sum(Daily Net), 0, RowNo())), Department, (Date, (TEXT, Ascending))))

I should mention I stated the date format incorrectly originally - the format I have is YYYY/MM/DD, so the Text sort used in my solution conveniently works.

Re: Maximum of Cumulative Sums in a dynamic date range

elijahabel — Thu, 28 Jun 2018 18:56:04 GMT

Thank you for the response, I mentioned the correct formula above. This was on the right track though.