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

      END

      ** 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)

      END

      ** 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()))

      THEN

      SUM([Gross Margin Target])

      END

      ** 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'

      ELSE

      'HIDE'

      END

      ** 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.

       

      Annabelle