Qlik Community

Qlik Sense App Development

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

elijahabel
New Contributor III

Aggr Using 2 cumulative functions

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:

DateDepartmentPNL
2018/06/03A50
2018/06/04A60
2018/06/05A10
2018/06/06A-50
2018/06/07A20

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:

DateDepartmentPNLCumulative PNLMax(Cumulative)DrawdownMax Drawdown
2018/06/03A50505000
2018/06/04A6011011000
2018/06/05A1012012000
2018/06/06A-5070120-50-50
2018/06/07A2090120-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:

DepartmentPNL
Max(CumulativeDrawdownMax Drawdown
A90120-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))))

1 Solution

Accepted Solutions
elijahabel
New Contributor III

Re: Aggr Using 2 cumulative functions

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

3 Replies

Re: Aggr Using 2 cumulative functions

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
New Contributor III

Re: Aggr Using 2 cumulative functions

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

Re: Aggr Using 2 cumulative functions

No problem, I am glad I was able to help

Community Browser