1 Reply Latest reply on Oct 28, 2016 3:58 PM by diego.medrano

    targets MTD and MTD last year

    Annabelle Rincon

      For my first Viz on the Tableau's community, I propose a simple Viz in order to monitor the monthly sales and the achievement of the Target, but also to see how we are performing comparing to last year.


      The tricky thing here, is that we wanted to compared for the current month (june in the example) the sales on a MTD basis and the MTD 2015 and we wanted to have also the full month (june) 2015 and the full target (june 2016).


      So the measures to take into account are :

      - the amount of sales  for the last 4 months (current month June 2016 : 15 160 $)

      - the amount of sales last year  for the same last 3 months but MTD for the current month (June 2015 MTD : 29 609 $)

      - the total of sales for the full current month for last year ( all June 2015 : 55 786 $)

      - the global targets for the full current month this year (full June 2016 target : 38 822 $)


      Screen Shot 2016-07-06 at 11.37.57.png

      1- Measures


      Create the following measures :


      Gross Margin MTD

      IF MONTH([Time Date])= MONTH(today()-1) and DAY([Time Date])>DAY(TODAY()-1)

      THEN 0

      ELSE quantity*margin


      ** calculates for the current month (june in the example), the Gross Margin June 2016 and June 2015 MTD**


      Gross Margin full month previous year

      IF ATTR(MONTH(Time Date))=ATTR(MONTH(today()))

      and ATTR(YEAR(Time Date)))=ATTR(YEAR(today()))-1

      THEN SUM(gross margin)


      ** calculates the Gross Margin in June 2015 full month**


      Target full month

      IF ATTR(MONTH([Time Date])) = ATTR(MONTH(TODAY()))

      AND ATTR(YEAR([Time Date])) = ATTR(YEAR(TODAY()))


      SUM([Gross Margin Target])


      ** calculates the target Gross Margin June 2016 full month**



      2- Filter


      As we want only the last 4 months to be displayed, we have to created the following filter :


      4 previous months and MTD

      IF MONTH(TODAY())>=5 and MONTH(TODAY())- MONTH([Time Date])<4 and MONTH([Time Date])<=month(TODAY()) then 'SHOW'

      ELSEIF  (MONTH(TODAY()-1)<5 and MONTH([Time Date])>=MONTH(DATEADD('month',-4,today()-1))) then 'SHOW'




      ** show only the last 4 month March to June but for every year on the data base, so you have to add a filter to only consider 2016 and 2015 **



      3- Build the VIZ


      Column : SUM( Gross Margin MTD)

      rows : Month(Time Date) and Year(Time Date)

      color : Year



      But here, the month and the year are not sorted as I wished, because I wanted the current month first.


      Screen Shot 2016-09-28 at 13.51.54.png


      so you have to create the following calculated field:


      Time date string


      STR(YEAR(Time Date)) + STR(MONTH(Time Date))


      Then you can use it to sort the Month and the year on the row window, like below.


      Screen Shot 2016-09-28 at 13.49.13.png


      You only have now to add the two measures : Targets full month and Gross Margin full month previous year in details and the two vertical lines will appear. You can set a different color if you wish.

      I hope it was useful.