3 Replies Latest reply on Feb 6, 2017 6:01 AM by Shinichiro Murakami

    Offsetting a date start

    Isa Saramago

      Hi guys,

       

      I'm new at using Tableau (about 2 weeks now), so please bare with me at my very newbie questions (and also regarding my English as this is not my native language).

       

      I'm already getting my hands on the Challenge Workbook and I'm encountering several difficulties.

      For instance in the Time Series Alignment challenge, of which I have attached the solution, I can't understand how it is possible to have two Calculated Fields (Company Start and First Date) that says exactly the same, and are used in the Offset Start Table Calculation. How is this doing what is supposed to do? A repeated calculated field used in the same expression....

       

      Thanks in advance.

       

      Message was edited by: Isa Saramago

        • 1. Re: Offsetting a date start
          Shinichiro Murakami

          This is quite a bit tough challenge for beginners.

           

          Whole the concept is

           

          1. Determine the minimum (oldest) date across all the dates.

          [FirstDate]

          WINDOW_MIN(MIN([Date]))

           

          2. Determine the minimum (oldest) date for respective company.

          [CompanyStart]

          WINDOW_MIN(MIN([Date]))

           

          3. Calculate the difference of "years" between (2) and (1) for respective company

          [Days]

          DATEDIFF('year',DATETRUNC('year',[FirstDate]),DATETRUNC('year',[CompanyStart]))

           

          4. Normalize (2) to (1) by subtracting (3) from (2)

          [OffsetStart]

          DATEADD('year',-[Days],MIN(DATETRUNC('year',[Date])))

           

           

          Confusion is actually as you mentioned,  two calculated fields are showing exactly same formula.

          This is functional at Table calculation setting, because combined tale calculation can be set in the table, with different definition for respective field.

           

          You can see the difference in this screen.

           

           

           

           

           

          [CompanyStart]

          WINDOW_MIN(MIN([Date]))

          with setting re-start at every company makes the calculation results as "First Date" of each Company.

           

          Instead, 

          [FirstDate]

          WINDOW_MIN(MIN([Date]))

          with setting no-re-starting makes the calculation results as "First Date" of all dates.

           

          With those different settings for respective table calculations, the calculation results of

          [Days]

          DATEDIFF('year',DATETRUNC('year',[FirstDate]),DATETRUNC('year',[CompanyStart]))

          makes different by company and as a result,

           

          [OffsetStart]

          DATEADD('year',-[Days],MIN(DATETRUNC('year',[Date])))

          works appropriately.

           

          "Datetrunc" is forcing all the dates from "yyyy/1/1" ~ "yyyy/12/31" to yyyy/1/1.  which means picking up only "year" part to make the table simple.

           

          Date Functions

          DATETRUNC(date_part, date, [start_of_week])

          Truncates the specified date to the accuracy specified by the date_part. This function returns a new date. For example, when you truncate a date that is in the middle of the month at the month level, this function returns the first day of the month. The start_of_week parameter is optional. If it is omitted, the start of week is determined by the data source. See Date Properties for a Data Source.

          Examples

          DATETRUNC('quarter', #2004-08-15#) = 2004-07-01 12:00:00 AM

          DATETRUNC('month', #2004-04-15#) = 2004-04-01 12:00:00 AM

           

          Hope this explanation helps.

           

           

          BTW, for other solution, using LOD is much much easier to understand the logic and easy to set for me.

           

          [First Date Company LOD]

          {fixed [Company]:min([Date])}

           

          [First Date Overall LOD]

          {fixed:min([Date])}

           

          [Day Difference LOD]

          [First Date Company LOD]-[First Date Overall LOD]

           

          [Offest LOD]

          datetrunc('year',[Date]-[Day Difference LOD])

           

           

           

          Thanks,

          Shin

          2 of 2 people found this helpful
          • 2. Re: Offsetting a date start
            Isa Saramago

            Thank you so much, Shin. Great explanation.

            • 3. Re: Offsetting a date start
              Shinichiro Murakami

              Isa,

               

              You are welcome.

              And one more thing.

              Could you mark my answer as correct to close this thread.

               

              Thanks,

              Shin