topic Re: Creating Max Drawdown calculation in QlikView

Creating Max Drawdown calculation

Fri, 19 May 2017 09:58:34 GMT

Hi all,

I'm looking to find out if anyone can help with creating a Max Drawdown calculation please.

I've attached a spreadsheet that lists the following -

2x tabs are a snapshot of the tables that would be coming in (Values,Indate)

1x Tab to show the Excel workings of how you calculate Max Drawdown on that data set.

Importantly each Product has an Indate that is when the Max Drawdown needs to start (and has a vast array of dates and values for before that date. So I need to apply either Set/Aggr/If to define the range to look at as well.

I've searched and worked through the current examples on here about Drawdown but they havent worked.

Hoping you guys can help,

Thanks!

Ryan

Re: Creating Max Drawdown calculation

sunny_talwar — Fri, 19 May 2017 15:17:41 GMT

2 questions for you

1) Do you want this to be done strictly on the front end?

2) InDate is 6/1/2015, but the 'Workings' tab expressions use no sort of logic to incoporate InDate? Is that by mistake or am I missing something here?

Re: Creating Max Drawdown calculation

Fri, 19 May 2017 15:25:39 GMT

Hi Sunny,

Thanks for replying,

1) Nope, happy to backend it

2) Its because currently they manually edit down the data once exported to excel so the Indate was part of the export (leaving them with just that dataset) I changed the date to be mid way through the dataset to be more realistic to what I'm working with, as in I have data for the last 10 years, but the calculations must start from the InDate, does that make sense?

Many thanks

Ryan

Re: Creating Max Drawdown calculation

sunny_talwar — Fri, 19 May 2017 15:30:25 GMT

So the data before InDate is not relevant then? Make sense... will try to get a sample across in a little bit

Re: Creating Max Drawdown calculation

Fri, 19 May 2017 15:32:48 GMT

Yeh exactly, it was because I needed to illustrate the requirement to use the date to define the range, else I'd have got replies that didn't take that into consideration and just used the full data set Awesome thank you!

Re: Creating Max Drawdown calculation

sunny_talwar — Fri, 19 May 2017 15:51:52 GMT

Try this script

Table1:

LOAD Date,

Value,

Product

FROM

[..\..\Downloads\TestData.xlsx]

(ooxml, embedded labels, table is Values);

Left Join (Table1)

LOAD Product,

InDate

FROM

[..\..\Downloads\TestData.xlsx]

(ooxml, embedded labels, table is InDate);

TempTable:

LOAD Product,

Date,

Value,

If(Product = Previous(Product), RangeSum(Peek('Ret + 1'), Log(1+Value)), Log(1+Value)) as [Ret + 1]

Resident Table1

Where Date >= InDate

Order By Product, Date;

TempTable2:

LOAD Product,

Date,

Value,

Exp([Ret + 1]) as [Ret + 1],

If(Product = Previous(Product), If(Exp([Ret + 1]) <= Peek('Ret + 1 New'), Peek('Ret + 1 New'), Exp([Ret + 1])), Exp([Ret + 1])) as [Ret + 1 New],

Exp([Ret + 1])/If(Exp([Ret + 1]) <= Peek('Ret + 1 New'), Peek('Ret + 1 New'), Exp([Ret + 1])) - 1 as temp

// If(Product = Previous(Product),

// If(Exp([Ret + 1])/If(Exp([Ret + 1]) <= Peek('Ret + 1 New'), Peek('Ret + 1 New'), Exp([Ret + 1])) - 1 < Peek('Max Draw'),

// Exp([Ret + 1])/If(Exp([Ret + 1]) <= Peek('Ret + 1 New'), Peek('Ret + 1 New'), Exp([Ret + 1])) - 1,

// Peek('Max Draw')), 0) as [Max Draw]

Resident TempTable;

Left Join (Table1)

LOAD Product,

Min(temp) as [Max Draw]

Resident TempTable2

Group By Product;

DROP Table TempTable, TempTable2;

Re: Creating Max Drawdown calculation

Mon, 22 May 2017 21:37:07 GMT

Worked like a charm, absolute hero thank you!