KNOWLEDGE BASE

Calculating Compounding Daily Returns


Published: 14 Dec 2015
Last Modified Date: 10 Mar 2017

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

Additional Information

Additional information about how calculations work is written into the calculated fields in the attached workbook.
Did this article resolve the issue?