1 2 Previous Next 17 Replies Latest reply on Aug 14, 2018 6:53 AM by kevin.blank.0

# Calculating drawdown within cumulative data

Hi all,

I'm hoping someone can provide some help to me as a fairly novice Tableau viz user. Please see the picture below, which I have attempted to demonstrate the concept that I am trying to figure out how to calculate within Tableau. In this example you could assume that both the max % drawdown and the longest drawdown period are the same drawdown (marked by the red lines), but this is often not the case. As soon as the price makes a new high and takes out the prior peak, this marks the end of the drawdown period.

Regarding the longest drawdown (or time to recover "to prior peak"), this is not necessarily the deepest % decline (as above), but the longest period (in time) that the price remains below it's previous peak.

I can achieve this in Excel (see attached spreadsheet), but I can't work out how to transition this into Tableau.

Thanks!

• ###### 1. Re: Calculating drawdown within cumulative data

Eric if you add a price field to your data I can help you work out the Tableau calculations and recreate that chart.

--Shawn

• ###### 2. Re: Calculating drawdown within cumulative data

Hi Shawn,

I have attached a workbook which I'm hoping you can use to attempt to calculate these metrics for me.

• ###### 3. Re: Calculating drawdown within cumulative data

Eric, this one is custom made for Joe since he's doing this sort of thing everyday. He works on these in the evenings, so don't expect an answer until tomorrow. (Joe be aware that he's turned off automatic update, so all the sheets come up blank to begin with.)

--Shawn

• ###### 4. Re: Calculating drawdown within cumulative data

Thanks Shawn,

Joe, I replaced the file with one that auto updates to avoid confusion.

• ###### 5. Re: Calculating drawdown within cumulative data

Just bumping this topic to see if anyone had any ideas? Thanks!

• ###### 6. Re: Calculating drawdown within cumulative data

Hi Eric,

Are you still looking for help with this?

Jonathan

• ###### 7. Re: Calculating drawdown within cumulative data

Hi Jonathan,

Yes I am still hoping for help on this if at all possible.

Best Regards,

Eric

• ###### 8. Re: Calculating drawdown within cumulative data

I've got a couple of questions: The Excel spreadsheet is at the level of the day, and the data is at the level of seconds. Do you want the drawdown time calculated by days or seconds? If days, then what price would be used for each day? If seconds, do you want to display the time in seconds, minutes, hours, hh:mm, hh:mm:ss? (I ask because Tableau doesn't have a built-in-function for that).

Jonathan

• ###### 9. Re: Calculating drawdown within cumulative data

Hi Jonathan,

I don't really mind which format the data is in so I guess it is just down to the easiest route based on the data.

If calculated in days, could you take the last of the intraday values? Alternatively, if calculated in seconds, then hours would probably be fine if they cannot be converted into days.

Eric

• ###### 10. Re: Calculating drawdown within cumulative data

Hi Jonathan,

Just checking back in here to see if you ever had a chance to work this one out?

Best Regards,

Eric Davidson

• ###### 11. Re: Calculating drawdown within cumulative data

Hi Eric,

I'd missed your earlier reply, I apologize. I'll have a chance to look at this over the weekend.

Jonathan

• ###### 12. Re: Calculating drawdown within cumulative data

Great!  Thanks Jonathan.

Eric

• ###### 13. Re: Calculating drawdown within cumulative data

Hi Eric,

Are you looking to do this calculation on the price or on the CumNetProfitAdj field? If it's the latter, it's a lot more complicated than the former.

Jonathan

• ###### 14. Re: Calculating drawdown within cumulative data

Hi Jonathan,

Unfortunately it is the CumNetProfitAdj field as I want to be able to calculate the drawdowns corresponding to the line chart (which is built from that field) in the workbook attached above.

The price field in this instance is simply the \$ price of each underlying stock being traded in this example, whereas the CumNetProfitAdj represents the profit or loss from each trade, cumulatively through time.

Look forward to hearing back from you when you get a chance.

Thank you!

Eric

1 2 Previous Next