7 Replies Latest reply on Jul 25, 2016 12:32 PM by Mark Worthen

    Datediff with a calculated date?

    Mark Worthen

      I have a simple data set with which I am working (highly simplified version of the real data) and am new to Tableau so forgive ignorance.

       

      I have projects, milestones and target dates.  As the target dates may change over time, I have a calculated field for determining the prior target date (changes at each project/milestone combo).  Not wanting the first calculated prior date to be null, I then added another column (perhaps it could have been done as one calc but I don't know), which IFNULL's the Prior Target Date and gives me the Target Date if it is null.

       

      So I have:

      Project 1     Milestone 1     Target Date 1     Target Date 1

      Project 1     Milestone 1     Target Date 2     Target Date 1

      Project 1     Milestone 1     Target Date 3     Target Date 2

      ...

       

      Now I want to calculate the number of days the target date has moved (either positive or negative).  I created another calculated field of DATEDIFF('day', ATTR([Target Date]), [Prior Target])

       

      This works perfect for the first Project/Milestone combo but the first record at each change in Project/Milestone is bunged up.  Records subsequent to the change are good but that first record is wrong.

       

      I've attached my sample workbook.  Can someone point out what I'm doing wrong?

       

      Thank you.

        • 1. Re: Datediff with a calculated date?
          Joe Oppelt

          You need to tell the table calc to restart every milestone.

           

          If the results in the attached are what you need, we can discuss how to do this.

          1 of 1 people found this helpful
          • 2. Re: Datediff with a calculated date?
            Mark Worthen

            Never mind.  I figured it out.  I had to edit the DateDiff in-table calculation after putting it on the Text tray.  Edit Table Calculation; Advanced; Deepest, Restart every Milestone and it works as desired.

            • 3. Re: Datediff with a calculated date?
              Mark Worthen

              Thanks Joe, I realized after thinking about it a bit that I probably needed to do the same thing for my DateDiff calc that I had to do to get my Prior Date Calc to work properly.

               

              Thank you for the quick response.

              • 4. Re: Datediff with a calculated date?
                Luciano Vasconcelos

                I'm curious.

                I tried to do this and it doesn't work then i changed Prior Target Date to:

                 

                If ATTR( [Milestone] ) = LOOKUP(Attr([Milestone]), -1) then LOOKUP(Attr([Target Date]), -1)

                Else

                    ATTR( [Target Date] )

                End

                 

                And it worked.

                 

                What did you do?

                1 of 1 people found this helpful
                • 5. Re: Datediff with a calculated date?
                  Mark Worthen

                  I changed the "Compute using:" value from "Table (Down)" to "Advanced".  In Advanced added Product, Milestone and then Target Date (in that order) into the Addressing section and clicked OK.  Back on the main calculation screen changed the "Restarting every:" from None to "Milestone".

                   

                  Worked at that point.

                  1 of 1 people found this helpful
                  • 6. Re: Datediff with a calculated date?
                    Mark Worthen

                    In working with it, I've decided I like your solution better.  I'll make another post outlining differences.

                    • 7. Re: Datediff with a calculated date?
                      Mark Worthen

                      I solved one way & Luciano provided another.  Both work but there are differences.

                       

                      My option:

                      I changed the "Compute using:" value from "Table (Down)" to "Advanced".  In Advanced added Product, Milestone and then Target Date (in that order) into the Addressing section and clicked OK.  Back on the main calculation screen changed the "Restarting every:" from None to "Milestone".

                       

                      Luciano's option:

                      If ATTR( [Milestone] ) = LOOKUP(Attr([Milestone]), -1) then LOOKUP(Attr([Target Date]), -1)

                      Else

                          ATTR( [Target Date] )

                      End

                       

                      After working with the data and making some changes in the more full-feature data set table I have, I found that in my option, anytime I make a change to the data in the table, I have to redo my steps to take that into account otherwise the data becomes invalid.

                       

                      However, using Luciano's option, the code defines the conditions, I just have to make sure the calculation is down rather than across and no matter if I add one additional dimension, remove a dimension, etc. the values stay consistent.

                       

                      Perhaps most people knew this but for me, as a beginner, it was a learning experience.

                      1 of 1 people found this helpful