13 Replies Latest reply on Mar 7, 2017 5:35 AM by Ankit Goyal

# Trouble with a date calc!

Hey there Community...need some help on this one....

I'm trying to build a column chart (vertical bars) with sales on the y-axis and the last 3 months on the x-axis.  Each bar would represent total sales per month.  Pretty simple and straightforward so far.

Now, what I need to do is add a second mark to each bar (I would choose a circle shape for instance) that would show the total sales for the same month 1 year ago.

problem is that when I use month of transaction date on the x-axis, Tableau recognizes that as the current year and can't plot the prior year mark.

Any help is GREATLY appreciated!!

thanks

• ###### 1. Re: Trouble with a date calc!

Find my approach below as reference stored in attached workbook version 9.3 located in the original thread.

1 of 1 people found this helpful
• ###### 2. Re: Trouble with a date calc!

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!

-Lisa

• ###### 3. Re: Trouble with a date calc!

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.

thoughts?

• ###### 4. Re: Trouble with a date calc!

It's always the same with these kids nowadays there is always a "However";))))

Did you apply the dynamic solution provided by Lisa Li That should solve your "However":)))

Regards,

Norbert

• ###### 5. Re: Trouble with a date calc!

thanks Lisa!  I'm a little confused...both of these calculated sales measures on columns and what on rows??

• ###### 6. Re: Trouble with a date calc!

Looking at Lisa's response now and am a bit confused...waiting for her response

thanks

• ###### 7. Re: Trouble with a date calc!

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?

• ###### 8. Re: Trouble with a date calc!

Find my updated workbook!

2016 dynamic:

if DATEDIFF("year",[date],TODAY())=1

and DATEDIFF("month",[date],TODAY())>=12 then [Sales] END

2017 dynamic

if DATEDIFF("year",[date],TODAY())=0

and DATEDIFF("month",[date],TODAY())>=0 then [Sales] END

1 of 1 people found this helpful
• ###### 9. Re: Trouble with a date calc!

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]

END

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

-Lisa

1 of 1 people found this helpful
• ###### 10. Re: Trouble with a date calc!

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!!

thanks

• ###### 11. Re: Trouble with a date calc!

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?

thanks

• ###### 12. Re: Trouble with a date calc!

That's not a problem.

2016 dynamic:

if DATEDIFF("month",[date],TODAY())>=13

and DATEDIFF("month",[date],TODAY())<=15 then [Sales] END

2017 dynamic:

if DATEDIFF("month",[date],TODAY())>=1

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.

Regards,

Norbert

• ###### 13. Re: Trouble with a date calc!

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

THEN 'TRUE'

END

Drag (3) in Filter shelf for "True" values only.

Let me know if this works for you!