4 Replies Latest reply on Jan 9, 2017 10:54 AM by Michelle Kosmicki

    YTD calcs Disappeared on Dashboard

    Michelle Kosmicki

      I have a question regarding YTD calculations. 

      Please Note: I inherited this dashboard, so I have no idea about the logic or coding behind it.

      *************************************************

      I have a client with a FY start in December. When I refreshed my dashboards with December data, the numbers for YTD disappeared. I was a bit shocked since I changed nothing, other than refreshing the data.

       

      Here is the calculation I have:

       

      If Month([Date])<Month(Today()) and Year([Date])=Year(Today()) then [Sessions] End

      I thought I found a solution and created this calculation:

      IF [Date] <= TODAY() AND DATEDIFF('year',[Date],Today())= 0 THEN [Sessions] END

      However, the calculations have the same result:

       

      I'm wondering if the data will begin to show up next month or if there is something else I should be doing? 

       

      Thanks!

       

      Michelle

        • 1. Re: YTD calcs Disappeared on Dashboard
          Christina Gremore

          Hi Michelle,

           

          The reason your first calculation wasn't working was that your value for MONTH(Today()) equals 1 (since it's January) and for the calc to return any results, the month of the record would have to be less than 1. There is no 0 month, so...no records.

           

          I'm not entirely sure why your second calc isn't returning results - are you positive that you actually have any data in your database where the Date is between 1/1/2017 and 1/5/2017? What happens if instead of using this filter (for now) you just put [Date] on the filter shelf > Relative Date > This year ?

           

          Your predecessor may have also put the original YTD Sessions field as a Data Source filter, so you'll want to make sure nothing is hiding in your data source filters.

           

          -Christina

          • 2. Re: YTD calcs Disappeared on Dashboard
            Michelle Kosmicki

            I was thinking that was the case, but I wasn't sure. This client has a FY that starts Dec 1st, so technically, I have 1 month of data (December) in the dashboards. Everything else is working just fine. It's the pesky YTD that is the issue.

             

            I'll double check the filtering as well.

             

            These are the same dashboards I used to stump the Tableau Docs at TC16. LOL :-)

            • 3. Re: YTD calcs Disappeared on Dashboard
              Christina Gremore

              Ah-ha! If you're basing it off the fiscal year, that explains why the second calc isn't returning December data like you want it to.

               

              Let's say you've got a row where [Date] = 12/11/2016. Let's see what would happen to it in your calc.

               

              [Date] <= TODAY() becomes # 12-11-2016 # <= # 1-5-2017 #

              December 11, 2016 is less than January 5, 2017, so that evaluates as TRUE, so that's good.

               

              DATEDIFF('year', [Date], TODAY()) = 0 becomes...

              DATEDIFF('year', # 12-11-2016 #, # 1-5-2017 #) = 2017 - 2016 = 1 <> 0 !!

              So this actually evaluates as false. The Datediff calculation doesn't take into account fiscal year; it only looks at calendar year.

               

              Because your December date doesn't meet both sets of criteria, you won't see any Sessions for those dates.

               

              To get this to work, you'll need to try a different approach. There are a few different ways you can do this, but here's what I just tested:

               

              1. Make sure that you've set your [Date] field's fiscal year start to December (right-click on Date in data pane > Default Properties > Fiscal year start)

              2. Create a custom date off of Date. Right-click on Date in data pane > Create > Custom Date > Detail = Years, Date Part. This will put a new field in your data pane called [Date (Years)] unless you decide to rename it

              3. Change the second clause of your IF statement to look like this:

              YEAR(TODAY()) - [Date (Years)] = 0

               

              Now, where this will get you into trouble is next December, because the TODAY() function will be calling the year 2017, when your fiscal calendar will have already moved along to 2018, which means you'll end up with a negative number and the calc will fail again. To get around that, create a new field, call it "As-Of Date" or "Today Date" or whatever - the calc should just be TODAY(), and then follow steps 1 & 2 to set the start of the fiscal year on the field then create a custom date, so that ultimately your second clause will look like this:

               

              [As-Of Date (Years)] - [Date (Years)] = 0

               

              Of course, if you have a dim table that has a separate field with the correct Fiscal Year for every date, leaning on that would be a way to simplify this whole process, but if you don't have that, following the steps outlined above will fake it for you.

               

              If this resolves your issue, please mark my answer as correct. Thanks!

               

              -Christina

              2 of 2 people found this helpful
              • 4. Re: YTD calcs Disappeared on Dashboard
                Michelle Kosmicki

                Thank you Christina Gremore!

                 

                The problem has been corrected. I'll need to set a reminder for next December so I can head of any date issues before they happen.

                 

                --Michelle