Maximum of Cumulative Sums in a dynamic date range

[elijahabel]

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,

90

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!

[jwjackso]

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

[balabhaskarqlik]

May be these expressions:

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

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

[elijahabel · Accepted solution]

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.

[elijahabel]

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