Aggr Using 2 cumulative functions

[elijahabel]

I am trying to complete the visualization calculation for Max Drawdown: https://www.investopedia.com/terms/m/maximum-drawdown-mdd.asp

Max Drawdown must be calculated dynamically when a user selects a date range, so I cannot calculate this in my load script.

Definitions:

Drawdown = Cumulative PNL - Maximum of Daily Cumulative PNL in a selected date range

Max Drawdown would be the negative Drawdown value with the greatest magnitude of this across a selected time series.

Assume my Data is like the below:

Date	Department	PNL
2018/06/03	A	50
2018/06/04	A	60
2018/06/05	A	10
2018/06/06	A	-50
2018/06/07	A	20

I have already successfully come up with a formula to calculate the Maximum of Daily Cumulative PNL in a selected date range:

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

For the above data, we would get the max cumulative of 120, and ideally the drawdown calculations below:

Date	Department	PNL	Cumulative PNL	Max(Cumulative)	Drawdown	Max Drawdown
2018/06/03	A	50	50	50	0	0
2018/06/04	A	60	110	110	0	0
2018/06/05	A	10	120	120	0	0
2018/06/06	A	-50	70	120	-50	-50
2018/06/07	A	20	90	120	-30	-50

How can I calculate Max Drawdown in a field if the table I am trying to put it in should look like the below with the date range of 2018/06/03 - 2018/06/07:

Department	PNL	Max(Cumulative	Drawdown	Max Drawdown
A	90	120	-30	-50
B
C
...

I have tried the following, but it does not give any numbers, only '-' in each cell of the column:

Min(Aggr(RangeSum(Above(Sum(DV01PNL), 0, RowNo())) - Max(RangeSum(Above(Sum(DV01PNL), 0, RowNo()))), DEPARTMENT, (REPORT_DATE, (TEXT, Ascending))))

[sunny_talwar]

May be this

Max(TOTAL <DEPARTMENT> Aggr(RangeMax(Above(Sum(DV01PNL), 0, RowNo())) - RangeMax(Above(Sum(DV01PNL), 0, RowNo())), DEPARTMENT, (REPORT_DATE, (TEXT, Ascending))))

[elijahabel · Accepted solution]

This almost worked, but definitely got me on the right track- just needed to use rangesum instead of sum for the cumulative sum. Thank you!

The formula that ended up working was:

Min(TOTAL <DEPARTMENT> Aggr(RangeSum(Above(Sum(DV01PNL), 0, RowNo())) - RangeMax(Above(RangeSum(Above(Sum(DV01PNL), 0, RowNo())))), DIVISION, (REPORT_DATE, (TEXT, Ascending))))

[sunny_talwar]

No problem, I am glad I was able to help