5 Replies Latest reply on Sep 13, 2018 7:40 AM by Michael Gillespie

    DATETRUNC & DATEPART- Explained: 'day', 'month', 'quarter', 'year' - The Dummy Version

    Stephen Groff

      I grew up in the 80s, coding was just getting started and I missed the bus by the early 90's because I was too busy watching reruns of Full House when I got home from school instead...  So my brain didn't develop this rapid lurch towards syntax knowledge like some of the new millennials... but I'm trying... with some help.


      I can read the descriptions found here: https://onlinehelp.tableau.com/current/pro/desktop/en-us/functions_functions_date.html


      However, I need an idiot-proof explanation... and scouring the internet doesn't provide me with vernacular that my brain can create relationships with, so I'm asking for your help.


      I know we don't all understand LODs, but I'll use it in this example:


      { FIXED [Date]:COUNTD([Serial Number])}



      { FIXED DATETRUNC('day', [Date]):COUNTD([Serial Number])}



      { FIXED DATETRUNC('month', [Date]):COUNTD([Serial Number])}



      { FIXED DATETRUNC('year', [Date]):COUNTD([Serial Number])}



      { FIXED DATEPART('day', [Date]):COUNTD([Serial Number])}



      { FIXED DATEPART('month', [Date]):COUNTD([Serial Number])}



      { FIXED DATETPART('year', [Date]):COUNTD([Serial Number])}



      What is this calculation syntax telling Tableau to do?


      When this calculation gives me a number, what does this number represent in the example context?

        • 1. Re: DATETRUNC & DATEPART- Explained: 'day', 'month', 'quarter', 'year' - The Dummy Version
          Michael Gillespie

          I love questions that make me think - thanks for asking this Stephen.


          There are a million ways to break down the answers but this is mine!


          Let's start with the difference between DATEPART and DATETRUNC.

          DATEPART returns an INTEGER value; DATETRUNC returns a DATE.

          So, since we're in September, a DATEPART calculation at the 'month' level of detail will return 9.

          DATETRUNC will return 2018-09-01 12:00:00 AM - the first date of the 'month' argument.


          With me so far?

          1 of 1 people found this helpful
          • 3. Re: DATETRUNC & DATEPART- Explained: 'day', 'month', 'quarter', 'year' - The Dummy Version
            Michael Gillespie

            Part 2: LODs


            One of the key concepts with an LOD calculation is that the calc is performed in relation to whatever is in the viz canvas.  It's a way of telling Tableau "I don't care WHAT I'm actually displaying in my viz, do it like this".  It's important to remember that, because it helps you interpret what you're seeing when you do the LOD.


            So, on to your question.

            { FIXED [Date]:COUNTD([Serial Number])} = For every value in [DATE], count the unique [Serial Number] values


            For the DATETRUNC LOD calcs:

            For today (September 13, 2108):

            DATETRUNC('day',today()) will return 13

            DATETRUNC('month'),today()) will return 9

            DATETRUNC('year'),today()) will return 2018


            The LODs are going to return the Distinct Count of Serial Numbers at the value of the "DAY" or "MONTH" or "YEAR" integer.  That means ALL Septembers (for instance), REGARDLESS of year.  That's because DATETRUNC returns "9" as an integer for the MONTH argument.


            The DATEPART LODs will return Distinct Counts but at a SPECIFIC DATE level of detail.

            So for today (September 13, 2108):

            DATEPART('day',today()) will return 2018-09-13

            DATEPART('month'),today()) will return 2018-09-01

            DATEPART('year'),today()) will return 2018-01-01


            The LODs will effectively convert the DATE value into the level of detail you've specified in the DATEPART function, and then COUNTD at that new level of detail (so anything with a month of '09' will get rolled up into a single number at the 2018-09-01 level).


            I believe that DATETRUNC and DATEPART at the 'year' level of detail will return the same value in these calcs but I'd have to test it to be certain.

            1 of 1 people found this helpful
            • 4. Re: DATETRUNC & DATEPART- Explained: 'day', 'month', 'quarter', 'year' - The Dummy Version
              Michael Gillespie

              Here's a sample workbook that illustrates all the calcs.

              1 of 1 people found this helpful
              • 5. Re: DATETRUNC & DATEPART- Explained: 'day', 'month', 'quarter', 'year' - The Dummy Version
                Michael Gillespie

                Quick answer for now as I have to run to a meeting.


                DATETRUNC is NOT giving you the "DAY" level of detail.  It's giving you the "DAY VALUE" level of detail.


                If your data set contains multiple months, DATETRUNC will add up all the '1' days, all the '2' days, etc.  So you'll get January 1 + February 1 + March 1 etc.  And if you have multiple years in your data you'll get ALL the January 1s + ALL the February 1s, etc.




                More later but look at the workbook I uploaded and try to understand why each column is doing what it's doing.  Think about the difference between what you're displaying in the viz and what you're telling each calc to do.

                1 of 1 people found this helpful