2 Replies Latest reply on Feb 19, 2018 8:26 PM by Duncan Lee

    DATETRUNC for year shifts the fiscal year

    Rohan Laas

      I want weeks part of the date drill down which, I am not sure, is possible with the built-in drill down. It is setup as Year --> Quarter --> Month --> Day what I want is Year --> Quarter --> Month --> Week --> Day

       

      What I have done is to create each part from my date field in the data.

       

      Year: DATE((DATETRUNC('year', [Date], 'monday')))

      Quarter: DATE((DATETRUNC('quarter',[Date],'monday')))

      Month: DATE(DATETRUNC('month',[Date],'monday'))

      Week: DATE(DATETRUNC('week',[Date],'monday'))

      Day: DATE(DATETRUNC('day',[Date],'monday'))

       

      I then added all of them to a hierarchy. The financial year starts in April and I have set it to April using Data--> Data Source --> Date Properties and by right clicking on each date type field and changing the "Fiscal Year Start" to April. Everything works except that if the date is for example, 2016-09-20 the Year shows FY 2016 and not FY 2017. Quarter is correct showing FY 2017 Q2

        • 1. Re: DATETRUNC for year shifts the fiscal year
          David Li

          Hi Rohan! The reason you're running into this is that currently, Tableau formulas ignore fiscal years. It's annoying, but that's how it is! So 9/20 truncates yearly to 1/1 instead of 7/1, and thus you get FY 2016 when you show it as a fiscal-yearly value. You don't run into this with the quarter because it comes out to the same value regardless of whether Tableau would ignore fiscal years or not.

           

          To get around this, you can use DATE() to manually build the date as July 1 of your year. Maybe something like this:

          DATE(STR(YEAR([Date])) + '-07-01')

          Let me know if that works!

          2 of 2 people found this helpful
          • 2. Re: DATETRUNC for year shifts the fiscal year
            Duncan Lee

            Btw this worked but I still needed to use that in conjunction with an IIF statement checking if MONTH([date]) >=7 or not.

             

            Thanks!