1 Reply Latest reply on Nov 6, 2018 9:34 PM by lei.chen.0

# Percent Change Since last month and quarter,year Calculation

hello everyone, i'm new to tableau. I'm working on a project which is being newly built in Tableau but already built in Qlikview , In tableau i will have to write the calculations to know the percentage value since last month and quarter, year.For which only weekdays are to be considered (weekends have to be excluded) Can someone please provide me these calculations in Tableau

Expected output ;

STOCK Names     DAILY RETURN VALUE   MTD RETURN     QTD RETURN      YTD RETURN    1 year      2 year

Example calculation in qlikview for MTD RETURN ;  which is =(\$(eToday)/\$(eMtdreturn))-1

calculation ;

[Sum({<Year=, Quarter=, Month=, Week=, KeyDate={'\$(=Date(max(KeyDate)))'}>} PX_Last )/Sum({\$<Year=, Quarter=,Week=,KeyDate={"\$(=Date(Max({<KeyDate = {'<\$(=MonthStart(Max(KeyDate)))'}>} KeyDate)))"}>} PX_Last)]-1

Example calculation in qlikview for DAILY RETURN ; which is =(\$(eToday)/\$(ePreviousday))-1

calculation ;

[Sum({<Year=, Quarter=, Month=, Week=, KeyDate={'\$(=Date(max(KeyDate)))'}>} PX_Last )/if(weekday(max(KeyDate))='Mon',Sum({<Year=, Quarter=, Month=, Week=, KeyDate={'\$(=Date(max(KeyDate)-3))'}>} PX_Last ),Sum({<Year=, Quarter=, Month=, Week=, KeyDate={'\$(=Date(max(KeyDate)-1))'}>} PX_Last ))]-1

Data Dictionary ;

 Price The price of the stock at a given time Rate of Return The percentage change in the price of the stock over a period of time Daily Return The percentage change in the price of the stock between the day observed and the weekday prior (Cell F6) Daily return: P[t]/P[t-1]-1 Monthly Return The percentage change in the price of the stock between the day observed and the same day of the prior month (Cell F7) Quarterly Return The percentage change in the price of the stock between the day observed and 63 market days prior (Cell F8) Annual Return The percentage change in the price of the stock between the day observed and the same day of the prior year (Cell F9) MTD Return The percentage change in the price of the stock between the day observed and the last weekday of  the prior month (Cell F11) QTD Return The percentage change in the price of the stock between the day observed and the last weekday of the prior quarter (Cell F12) YTD Return The percentage change in the price of the stock between the day observed and the last weekday of the prior year (Cell F13) Daily Return (Annualized) The "Daily Return" (as described in Cell B4) expressed in terms of annual return. In other words, what would our annual return be if the fund had this daily return every day for one year? Monthly Return (Annualized) The "Monthly Return" (as described in Cell B5) expressed in terms of annual return. In other words, what would our annual return be if the fund had this monthly return every month for one year? Quartely Return (Annualized) The "Quarterly Return" (as described in Cell B6) expressed in terms of annual return. In other words, what would our annual return be if the fund had this quarterly return every quarter for one year?

 Return Calculation ; Fill all missing weekday with previous weekday data (forward fill): t = today (weekday) t-1 = previous weekday Rate of Return 1.  Daily return: P[t]/P[t-1]-1 2. MTD return (month to date): P[t]/P[m-1]-1 a. P[m-1]is the last weekday of the previous month 3. QTD return (quarter to date): P[t]/P[g-1]-1 a.  P[g-1] last weekday of the previous quarter (March, June, September & December) 4. YTD return (year to date): P[t]/P[y-1]-1 a.  P[y-1]is last weekday of December of the previous year Rolling Returns 1 Year rolling return: P[t)/P[252 lookback]-1 2 Year rolling return: P[t]/P[252*2 lookback]-1 3 Year rolling return: P[t]/P[252*3 lookback]-1 5 year rolling return: P[t)/P[252*5 lookback]-1

Thank you,

• ###### 1. Re: Percent Change Since last month and quarter,year Calculation

Hello tez,

In general speaking, MTD, QTD, YTD calculations can be achieved by table calculation.

Here's the online help you can start with.

For further advice, please provide some sample data.

Regards

Lei