Try creating three calculations:
1. Returns Sales from past 3 months
if datediff('month' , [Date] , today())<=3 then [Sales] END
2. Return dates from past 3 months
if datediff('month', [Date] , today())<=3 then [Date] END
3. Return Sales from those months a year ago
if datediff('year', [Dates from past 3 months], today()) = 1 then [Sales] END
Then plot [Sales from past 3 months] and [Sales from those months a year ago] on a dual axis chart.
Hope this helps!
Hi Norbert and thanks for a speedy reply! I downloaded your workbook to see how you did it and then applied the same logic to my sample workbook. Sure enough, it works perfectly.
However (sorry, there's always a 'however' ), I need this to dynamically show the last 3 full months. So if it's today March 6 2017, then the 3 bars would be Dec2016, Jan2017 and Feb2017. This spans 2 years.
Or if it's June 29, 2017, then the bars would be Mar17, Apr17, May17. This one doesn't span years.
Your solution is very powerful but it looks like the filters would manually need to be updated each new month.
thanks Lisa! I'm a little confused...both of these calculated sales measures on columns and what on rows??
Looking at Lisa's response now and am a bit confused...waiting for her response
Hi Lisa - forget the last question...rows should have the 'dates from past 3 months' calculated field (your #2 above).
however, I still can't get it to work. Any chance you have a sample workbook that you can attach?
Find my updated workbook!
and DATEDIFF("month",[date],TODAY())>=12 then [Sales] END
and DATEDIFF("month",[date],TODAY())>=0 then [Sales] END
bars & balls_dynamic_nalmai.twbx 18.6 KB
I have attached my workbook and some screenshots in case you don't have Tableau 10.1.
I noticed an error with the calculations I described earlier, but here are the calculations I wrote to create these views.
1. [Sales from Past 3 Months]
if datediff('month' , [Order Date] , makedate(2013,7,1))<=3 then [Sales] END
2. [Sales Prior Year]
if datediff('month' , [Order Date] , makedate(2013,7,1))<=15 AND
datediff('month' , [Order Date] , makedate(2013,7,1))>=13 THEN [Sales]
Then I created this view:
To make a dual axis chart, right click on the green pill SUM([Sales Prior Year]), select Dual Axis. Then right click on the axis and make sure axes are synchronized.
Note, I used superstore data that does not include data relevant to today() so I used makedate(2013,7,1). For your purposes, you can replace all instances of that with today().
sample_dateCalc.twbx 1.5 MB
Thanks Lisa for the updated workbook and screenshots! While this isn't exactly what I'm looking for, this is a really awesome solution and I will be keeping your sample workbook handy for my future needs!!
That's it Norbert! The new/updated "dynamic" calculations are perfect! However (there it is again!), I need to exclude the current month and show only the proceeding 3 months. We are 7 days into March right now and we don't want to show partial months. So the bars should be Dec2016, Jan2017, and Feb2017, with both current year and prior year/same month amounts.
Any creative logic to do this?
That's not a problem.
and DATEDIFF("month",[date],TODAY())<=15 then [Sales] END
and DATEDIFF("month",[date],TODAY())<=3 then [Sales] END
But now we get a "viz-challenge". Please indicate how you would like to deal with the following situation.
Ideally you would like to solve this "dynamically" without any hardcoding.
I was able to do it quickly using 2-3 calculated fields, however, there may be more efficient options available. Here's how it did it:
Create 3 calc fields
(1) Month Name: DATENAME('month',[Date]): This will return the name of the month e.g. January, February etc.
(2) Max Days of Month: Use a case statement to define what are the maximum available days in a month, e.g. Jan should have 31 to be called as completed month, Feb >=28 etc.
(3) Completed Months: I'm checking if the max day value in (1) matches with (2):
IF(max(DATEPART('day',[Date])))=AVG([Max Days of Month])
Drag (3) in Filter shelf for "True" values only.
Let me know if this works for you!
CompletedMonths.twbx 11.4 KB