5 Replies Latest reply on Jan 27, 2014 4:34 PM by Dan Huff

    DATEDIFF counting over the year change

    Kim Trop

      DATEDIFF('weekday',[Pickup_DTTM],[AuditDelivery_DTTM] )-2*(DATEPART('week',

      [AuditDelivery_DTTM])-DATEPART('week',[Pickup_DTTM] ) )

       

      I used this calculated field to get a day count of the business days (Mon - Fri) between the Pickup_DTTM to the AuditDelivery_DTTM.  This worked fine until I added records where the pickup date was in Dec. 2013 and the delivery date is in Jan 2014.  For example records with a Pickup Date of 12/31/2013 and a Delivery Date of 1/2/2014 have a Date Count of 106.  It should be  2 days.  The records where both pickup and delivery in 2013 are working correctly.

       

      What am I missing?

       

      Thanks,

      Kim

      Dan Huff

        • 1. Re: DATEDIFF counting over the year change
          kettan

          I found this formula in  The specified item was not found.:

           

          ([AuditDelivery_DTTM]-[Pickup_DTTM])-
          if
          (DATEDIFF('year',[Pickup_DTTM], [AuditDelivery_DTTM]))>=1
          then
          (2*((DATEPART('week', [AuditDelivery_DTTM]) + 52*DATEDIFF('year',[Pickup_DTTM], [AuditDelivery_DTTM])) - DATEPART('week', [Pickup_DTTM])))
          else
          (2*(DATEPART('week', [AuditDelivery_DTTM]) -DATEPART('week', [Pickup_DTTM])))
          end
          
          
          
          
          
          

           

          The formula crashed my Tableau 8.0.4. Then I deleted all worksheets and created a new only containing involved fields. That helped. See attached. Hopefully this formula will not crash on your computer.

          .

          • 2. Re: DATEDIFF counting over the year change
            Kim Trop

            Thanks Kettan.   The calculation of the day count works but only if I wanted to use both the Pickup Date & Delivery date as dimensions in my report.  I'd have to show every combination of pickup/delivery date.  What I'm trying to do is use Pickup Date in the row and show for that specific pickup date, how many records have a day count of 1, #of records with day count of 2, etc.  This may be useful for a different report but not for this one.

             

            Thanks,

            Kim

            • 3. Re: DATEDIFF counting over the year change
              Dan Huff

              Kim--

               

              You can correct me if I am wrong, but I think this calculation will get you to where you need to be:

               

              DATEDIFF('weekday',[Pickup_DTTM],[AuditDelivery_DTTM] ) - (IF DATEPART('weekday',[AuditDelivery_DTTM])=7 THEN 1 ELSE 0 END) - 2 * datediff('week',[Pickup_DTTM],[AuditDelivery_DTTM])

               

              Verify this against your data and let me know if this works as you expect it to. The IF statement is meant to handle Saturdays since the datediff week calculation will not catch Saturdays since the weeks start on Sundays.

               

              I hope this helps,

               

              Dan

              • 4. Re: DATEDIFF counting over the year change
                Kim Trop

                That worked perfectly Dan.  Thanks for your help.

                • 5. Re: DATEDIFF counting over the year change
                  Dan Huff

                  Glad to hear it. Happy to help when I can.


                  Dan