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

# targets MTD and MTD last year

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 \$)

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.

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.

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

• ###### 1. Re: targets MTD and MTD last year

Thanks for sharing Annabelle! :-)

This would be great for our TabWiki