1 Reply Latest reply on Dec 3, 2018 2:23 AM by Ciara Brennan

    MTD, YTD, QTD Table calculation in Tableau

    Dinesh Anblazahan
      1. Is it good idea to calculate MTD, YTD,QTD using Table calculation in Tableau

      Answer is  “Yes” and “No”

      1. Yes à when you wanted a Static dashboard or a Dashboard without much complex navigation in that case you can use Table calculations.
      2. No à when your Dashboard has complex navigations and drilldowns in that case calculations using Table calculations may not scale up.
        1. When table calculations don’t scale up what options we have to do our calculations?

      Use Tableau Prep.

      To understand the above questions and answers in detail let’s take a requirement and analyze.

      Note:

      This topic is quite big, so I will try to cover this topic in two blogs. In this blog lets understand the first question and its answer.

       

      We had a reporting requirement of comparing the Actual Vs planned numbers it’s one of very common requirement, but this requirement gave us a real challenge because:

      1. At any point of time user like to compare either Actual(YTD) vs Plan(YTD) or Actual(MTD) vs Plan(MTD) or Actual(QTD) vs Plan(QTD).
      2. The Actual data from source had YTD numbers and the Plan data from source had MTD numbers.
      3. Source data doesn’t have date field.

      Actual:

      Actual.png

      The Actual Amount what you see in above table is YTD value i.e. The July Amount which is nothing but Jan + Feb + Mar + Apr +May + Jun +Jul Amount values.

      Plan:

      Plan.png

      The Plan Amount what you see in above table is MTD value.i.e.  Amount of that particular Month.

       

      Let’s get into Tableau to see how we tried to meet this requirement in Tableau.

      Note:

      • This in my first project in Tableau and to achieve this requirement there might be different other ways, so I am not saying mine is the best approach.
      • Apart from the above two sources (Actual & Plan) we also had other source which had Master data. I am not going to explain about that data because its irrelevant for our topic.

       

      In tableau first let’s combine all three sources: Actual, Plan, Master Data

      • Union of Actual and Plan Data.
      • The Union O/P is joined with master Data.

      data.png

       

      By doing this we have actual and Plan data next to each other. But we can’t compare actual and plan amount because Actual data is YTD value and Plan is MTD value.

      So, our next step is to create 4 more additional measures:

      1. Actual MTD
      2. Actual QTD
      3. Plan YTD
      4. Plan QTD

       

       

      So, by end of this process we will have totally 6 measures:

      1. Actual MTD
      2. Actual QTD
      3. Actual YTD (Original that came in Source)
      4. Plan MTD (Original that came in Source)
      5. Plan QTD
      6. Plan YTD

      With this user can easily compare any of these measures at any point of time.

      Let’s get into Tableau again and see how to create these measures.

      Under Measures section create new Calculated measure “Actual MTD” and the formula for the measure is:

      Actual MTD:

      IIF((IIF(INDEX()=1,SUM([Actual YTD]),IFNULL(SUM([Actual YTD]) - LOOKUP(ZN(SUM([Actual YTD])), -1),Sum([Actual YTD]))))>= 0,(IIF(INDEX()=1,SUM([Actual YTD]),IFNULL(SUM([Actual YTD]) -LOOKUP(ZN(SUM([Actual YTD])), -1),Sum([Actual YTD])))),0)

      fr1.png

      The logic for the Formula is kind of Fibonacci series and the only difference is instead of adding the adjacent value we must subtract it.

      For an example to get the MTD value of Feb we must subtract the YTD Feb value with YTD Jan value.

      xlx.PNG

      Feb = 180-100 = 80

      Mar = 300-180 = 120 ……

      1. In simple term logic is à( current value – Prev value) in tableau formula for that is:

      SUM([Actual YTD]) - LOOKUP(ZN(SUM([Actual YTD])), -1)

      This logic is good for all in between value but this logic will not work for 1st and last value because the 1st value is Jan and for JAN YTD= MTD we should not use this formula. Similarly, last value must be handled with care, you may think what’s wrong with last value. Let me explain a situation where actual data what we received from source is only till Aug and the plan data is till Dec. This is very much valid situation because actual data is received based on every month transaction and the plan data we will always have it till December.

      Let see the example data to understand it:

      xqe.png

      If we apply “current value – prev value” logic for month of May the actual MTD for May is -360 which is wrong. The logic will work till April but not for May. So we should always handle the last value with care. Ideally MTD for May should be “0”

      If you decipher the formula:

      IIF((IIF(INDEX()=1,SUM([Actual YTD]),IFNULL(SUM([Actual YTD]) - LOOKUP(ZN(SUM([Actual YTD])), -1),Sum([Actual YTD]))))>= 0,(IIF(INDEX()=1,SUM([Actual YTD]),IFNULL(SUM([Actual YTD]) -LOOKUP(ZN(SUM([Actual YTD])), -1),Sum([Actual YTD])))),0)

      you will understand it handles both 1st and last value special cases.

      Now next calculation will be Actual QTD, Under Measures section create new Calculated measure “Actual QTD” and the formula for the measure is:

      Actual QTD:

      CASE INDEX()

      WHEN 3 THEN ZN((LOOKUP(([Actual MTD]), FIRST()))) + ZN((LOOKUP(([Actual MTD]), FIRST()+1))) + ZN((LOOKUP(([Actual MTD]), FIRST()+2)))

      WHEN 6 THEN ZN((LOOKUP(([Actual MTD]), FIRST()+3))) + ZN((LOOKUP(([Actual MTD]), FIRST()+4))) + ZN((LOOKUP(([Actual MTD]), FIRST()+5)))

      WHEN 9 THEN ZN((LOOKUP(([Actual MTD]), FIRST()+6))) + ZN((LOOKUP(([Actual MTD]), FIRST()+7))) +ZN((LOOKUP(([Actual MTD]), FIRST()+8)))

      WHEN 12 THEN ZN((LOOKUP(([Actual MTD]), FIRST()+9))) + ZN((LOOKUP(([Actual MTD]), FIRST()+10))) + ZN((LOOKUP(([Actual MTD]), FIRST()+11)))

      ELSE 0 END

      rwgerg.png

      The logic for the Formula is every quarter month value is addition of all three months of that Quarter i.e March QTD value is JAN MTD + Feb MTD + March MTD.

      jjgj.png

      Now by end of this activity we have

      1. Actual MTD
      2. Actual QTD
      3. Actual YTD (Original that came in Source)

      Let’s work on Plan amounts, from source we receive Plan amounts as MTD values, so we must create YTD and QTD.

      Under Measures section create new Calculated measure “Plan YTD” and the formula for the measure is

      Plan YTD:

      RUNNING_SUM (SUM([Plan MTD]))

      sgteg.png

      The logic for the Formula is Fibonacci series generation.

      Now next calculation will be Plan QTD, Under Measures section create new Calculated measure “Plan QTD” and the formula for the measure is:

      thtrh.png

      We have all the required measures:

      1. Actual MTD
      2. Actual QTD
      3. Actual YTD (Original that came in Source)
      4. Plan MTD (Original that came in Source)
      5. Plan QTD
      6. Plan YTD

       

      With this user can compare MTD /YTD/QTD values any point of time.

      trhh.png

      Note:

      1. Since the measures are table calculations we must define how the calculation should run. When you pull the dimensions and measures to the table and if you don’t see the desired O/P then right click the Measure à Edit Table Calculation

      vgdtr.png

      And define the computation correctly. In my case the Table calculation definition for my measure looks like below

      gdhrh.PNG 

       

      Tableau provides sophisticated Table functions its great we can achieve most of our complex calculations using those functions. And these functions run fast, in our project we ran these run time calculations on huge volume of data with live connections and it ran fast.

       

      Having said all these the running table calculations are good only for static reports not for interactive reports. What do I mean by interactive reports

      1. Reports with Multiple hierarchies and user will drilldown on those hierarchies dynamically.
      2. User uses Keep only features on our charts
      3. User wanted actions like clicking on chart takes them to other table with filter values.

       

      Since Table functions are defined by characteristics and running calculations are based on dimensions dynamic drilldowns/filters by dimensions will break all the table calculations. For an example

       

      The O/P of our calculation is shown below:

      hfhg.png

      The columns marked yellow are all calculated columns.

      Let’s assume user click on month “March “and says “Keep only”

      yuhjhj.png

       

      Check the values of column marked in Yellow, which is nothing but the calculated columns.

      fggjk.png

      The moment user says keep only at the Month dimension the “March” filter value is push down to underlying table and filters out at source table level. And all our table calculations are running calculations which means to compute March MTD and QTD we need Jan, Feb and march value. Since the keep only has filtered out only March value all running calculation will not perform in the way we expected.

       

      From this what we can conclude is when calculation is simple and when you wanted a Static dashboard a Dashboard without much complex navigation in that case you can use Table calculations. And when calculation is complex and when your Dashboard has complex navigations and drilldowns in that case Table calculations may not scale up.

       

      Sooner I will write down my second blog on how we handled the same calculation in Tableau prep and why its good to handle the complex calculations in Tableau prep.