topic Re: Calculate Max Drawdown by ID in QlikView

Calculate Max Drawdown by ID

Tue, 17 Mar 2015 12:36:40 GMT

Hello everyone,

I need to calculate the max drawdown by ID with one expression. Drawdown is defined as:

I have been able to replicate the above table in QlikView, but not the table to the right, which is by ID only, not displaying the Date dimension.

Can anyone help?

Re: Calculate Max Drawdown by ID

maleksafa — Tue, 17 Mar 2015 12:39:21 GMT

max( total <ID> DrawDown)

Re: Calculate Max Drawdown by ID

Tue, 17 Mar 2015 12:42:14 GMT

Hi Malek,

What I need to do is calculate the max drawdown by ID only (see the table to the right, you may have to scroll over a bit to see it). In other words, it will be a different table.

Re: Calculate Max Drawdown by ID

robert_mika — Tue, 17 Mar 2015 12:45:59 GMT

t1:

LOAD * INLINE [

ID, Date, Value Change

A, 1/1/2015, -1

A, 1/2/2015, 86

A, 1/3/2015, -13

A, 1/4/2015, 150

A, 1/5/2015, -50

B, 1/1/2015, -9

B, 1/2/2015, 118

B, 1/3/2015, 17

B, 1/4/2015, 77

B, 1/5/2015, -20

C, 1/1/2015, -22

C, 1/2/2015, -35

C, 1/3/2015, 132

C, 1/4/2015, 97

C, 1/5/2015, 100

];

t2:

load

ID as MinID,

Min([Value Change]) as MinValue

resident t1

group by ID;

Re: Calculate Max Drawdown by ID

dhananjay_capge — Tue, 17 Mar 2015 12:47:38 GMT

max( total <ID> DrawDown)

Re: Calculate Max Drawdown by ID

Tue, 17 Mar 2015 14:01:36 GMT

Hi Robert,

This is a good idea, but it won't work for two reasons:

The population needs to be dynamic for the selection, including date
The max drawdown is not equal to the min value. It is the min MTD value compared to the max MTD value

I think it'll need to be an expression on the chart.

Re: Calculate Max Drawdown by ID

Tue, 17 Mar 2015 14:02:35 GMT

Hi Dhananjay,

See my reply to Malek above.