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

    Trouble with a date calc!

    Brad Sheridan

      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

      brad

        • 1. Re: Trouble with a date calc!
          Norbert Maijoor

          Hi Brad,


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

            Hey Brad,

             

            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

            CoEnterprise | Home

            • 3. Re: Trouble with a date calc!
              Brad Sheridan

              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?

              Brad

              • 4. Re: Trouble with a date calc!
                Norbert Maijoor

                Hi Brad,

                 

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

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

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

                     

                    thanks

                    • 7. Re: Trouble with a date calc!
                      Brad Sheridan

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

                        Hi Brad,

                         

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

                          Hey Brad,

                           

                          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

                          CoEnterprise | Home

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

                            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

                            Brad

                            • 11. Re: Trouble with a date calc!
                              Brad Sheridan

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

                                Hi Brad,

                                 

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

                                  Brad,

                                   

                                  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!