KNOWLEDGE BASE

Calculating Compounding Daily Returns

Published: 14 Dec 2015

Issue

How to calculate compound daily returns, where

Daily returns = a, b, c
Compounded return = (1+a)*(1+b)*(1+c) - 1

Environment

• Tableau Desktop

Resolution

The attached example workbook uses the sample data set Superstore to demonstrate calculating the compound daily returns restarting every month:

CREATE THE CALCULATIONS

1. Create a calculated field with a name like "Daily Returns" with a calculation similar to the following:

1 + SUM( [Sales] )

2. Create a calculated field with a name like "Compound Daily Returns Feeder" with a calculation similar to the following:

IF FIRST()= 0
THEN [Daily Returns]
ELSEIF [Daily Returns] != LOOKUP( [Daily Returns], -1 )
THEN PREVIOUS_VALUE([Daily Returns]) * [Daily Returns]
ELSE PREVIOUS_VALUE([Daily Returns])
END

3. Create a calculated field with a name like "Compound Daily Returns" with a calculation similar to the following:

PREVIOUS_VALUE( LOOKUP( [Compound Daily Returns Feeder] - 1, LAST()))

4. Create a calculated field with a name like "First Filter" with a calculation similar to the following:

FIRST() = 0

CREATE THE VIEW

1. Drag [Order Date] to the Rows shelf

2. Right-click [Order Date] on the Rows shelf and select Month

3. Drag another copy of [Order Date] to the Rows shelf

4. Right-click [Order Date] on the Rows shelf and select Day

5. Drag [Compound Daily Returns] to Text on the marks card

6. Right-click [Compound Daily Returns] on Text and select Compute Using > Pane (Down)

7. Drag [First Filter] to the Filters shelf and click OK

8. Right-click on [First Filter] on the Filters shelf and select Compute Using > Pane (Down)

9. Check True and click OK

10. Right-click DAY(Order Date) on the Rows shelf and uncheck Show Header