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 $)
Create the following measures :
Gross Margin MTD
IF MONTH([Time Date])= MONTH(today()-1) and DAY([Time Date])>DAY(TODAY()-1)
** 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**
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.
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.