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

    Percent Change Since last month and quarter,year Calculation

    tez naga

      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 ;

       

      PriceThe price of the stock at a given time
      Rate of ReturnThe percentage change in the price of the stock over a period of time
      Daily ReturnThe 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 ReturnThe percentage change in the price of the stock between the day observed and the same day of the prior month (Cell F7)
      Quarterly ReturnThe percentage change in the price of the stock between the day observed and 63 market days prior (Cell F8)
      Annual ReturnThe percentage change in the price of the stock between the day observed and the same day of the prior year (Cell F9)
      MTD ReturnThe percentage change in the price of the stock between the day observed and the last weekday of  the prior month (Cell F11)
      QTD ReturnThe percentage change in the price of the stock between the day observed and the last weekday of the prior quarter (Cell F12)
      YTD ReturnThe 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,