2 Replies Latest reply on Aug 13, 2018 7:43 AM by Okechukwu Ossai

# How to calculate cumulative return and display it?

Hi, I have some data like this:

标题 1标题 2标题 3
DATESTOCKRETURN
20180101A0.02
20180102A0.03
20180101B0.04
20180102B-0.02

How to calculate cumulative return for different stock, like STOCK A: (1 + 0.02)(1 + 0.03) - 1 = 0.0506. And display each stock return in bar plot?

Thanks!

• ###### 1. Re: How to calculate cumulative return and display it?

I can't claim any credit for this but the calculated field you want is:

{ FIXED [Stock] : EXP(SUM(LN(1+[Return]))) - 1}

Drag it to dimensions, or create another calculated to bucket the results and you will be able to use this as the axis of your bar chart.

Full credit for the formula go to Brian Bell who posted the genius solution here: https://community.tableau.com/ideas/1857

1 of 1 people found this helpful
• ###### 2. Re: How to calculate cumulative return and display it?

I went through a longer route which will be an issue if there are several dates involved. Brian Bell's solution obviously looks more elegant and robust.

[Initial Return]

{FIXED [Stock]: MAX(IF {FIXED [Stock]: MIN([Date])} = [Date] THEN [Return] END)}

[Last Return]

{FIXED [Stock]: MAX(IF {FIXED [Stock]: MAX([Date])} = [Date] THEN [Return] END)}

[Cumulative Return]

{FIXED [Stock]: MIN((1+[Initial Return]) * (1+[Last Return]) - 1)}