12 Replies Latest reply on Apr 25, 2018 10:38 AM by Okechukwu Ossai

    Dateadd

    Lanre Olubajo

      Hi

      I have a scenario where standard test days is 5 days from the date testing started. Now I want to write a calculated field where it will should how many days the testing is overdue after the standard 5 days, not counting weekends, just Monday to Friday.

      Thank you in advance

        • 1. Re: Dateadd
          Lanre Olubajo

          Apologies, there was a typo. I meant to say where it "show" and not should.

          • 2. Re: Dateadd
            Sourav Banerjee

            Take week days in demention, at filter exclude the week days when you want to display the data .

            • 3. Re: Dateadd
              sudheer.kumar.5

              Hello Olusegun,

               

              Here I am attaching the .TWBX file which will help to find the overdue days. I am not sure about counting the weekends in your case. Following your thread for the answer .

               

              Regards,

              Sudheer

              • 4. Re: Dateadd
                Ritesh Bisht

                This Calculation is working for me

                 

                 

                MINUS WEEKEND=

                 

                IF

                [DateName]='Monday'  then  [DateDiff] - 2 * (FLOOR([DateDiff]/7))  ELSEIF

                [DateName]='Tuesday'  then [DateDiff] - 2 * (FLOOR(([DateDiff]+1)/7))

                ELSEIF [DateName]='Wednesday'  then [DateDiff] - 2 * (FLOOR(([DateDiff]+2)/7))

                ELSEIF  [DateName]='Thursday'  then [DateDiff] - 2 * (FLOOR(([DateDiff]+3)/7))

                ELSEIF [DateName]='Friday'  then [DateDiff] - 2 * (FLOOR(([DateDiff]+4)/7))

                END

                 

                 

                Note DATENAME=

                DATENAME('weekday',[Date])

                 

                 

                DATEDIFF=

                DATEDIFF('day',[Date],[Today])+1

                 

                 

                 

                Screen Shot 2018-04-23 at 3.31.44 PM.png

                 

                 

                Please mark it as HELPFUL/CORRECT if it helped you so that it can help others as well

                 

                Thanks,

                Ritesh

                • 5. Re: Dateadd
                  Ritesh Bisht

                  Please mark it as CORRECT/HELPFUL & close the thread so that it can help others as well

                   

                  Thanks,

                  Ritesh

                  • 6. Re: Dateadd
                    Okechukwu Ossai

                    Hi Olusegun,

                     

                    I assume you have 2 date fields; Test Start Date and Test End Date. I also assume that some tests have been completed while others are open. With these assumptions in mind, I suggest you try the solution below. I have broken it into various steps to make it easy to follow. Let me know if you have only 1 date field and I'll modify the solution for you.

                     

                    Step 1: Create calculated field called [End Date (Today)]

                    IF ISNULL([End Date]) THEN TODAY()

                    ELSEIF NOT ISNULL([End Date]) THEN [End Date]

                    END

                    What this formula does is to add Today's date to all open tests where End Date is null. This will be used to calculate test duration in subsequent steps.

                     

                    Step 2: Create calculated field [Start Date (Shifted to Weekday)]

                    IF DATEPART('weekday', [Start Date]) = 1 THEN DATEADD('day', 1, [Start Date])

                    ELSEIF  DATEPART('weekday', [Start Date]) = 7 THEN DATEADD('day', 2, [Start Date])

                    ELSE [Start Date]

                    END

                    This calculation will shift any test starting on Saturday or Sunday to next Monday.

                     

                    Step 3: Create calculated field [End Date (Shifted to Weekday)]

                    IF DATEPART('weekday', [End Date (Today)]) = 1 THEN DATEADD('day', -2, [End Date (Today)])

                    ELSEIF  DATEPART('weekday', [End Date (Today)]) = 7 THEN DATEADD('day', -1, [End Date (Today)])

                    ELSE [End Date (Today)]

                    END

                    This calculation will shift any test ending on Saturday or Sunday to previous Friday.

                     

                    Step 4: Create calculated field [Test Duration]

                    MIN(DATEDIFF('day', [Start Date (Shifted to Weekday)], [End Date (Shifted to Weekday)]) + 1

                    - 2 * DATEDIFF('week', [Start Date (Shifted to Weekday)], [End Date (Shifted to Weekday)]))

                    The first datediff calculates duration in days between start and end test dates. 1 was added since datediff doesn't count the first date. Replace this with 0 if you want to exclude the first date from test duration. The second datediff counts the number of weeks, then multiplied by 2 since there are 2 weekends in one week. The difference calculates business days (excluding weekends). The formula is wrapped in MIN() to ensure result aggregation is always correct, since I don't know what the grain of your data is.

                     

                    Step 5: Create calculated field [Days Overdue]

                    IF [Test Duration] <= 5 THEN 0

                    ELSEIF [Test Duration] > 5 THEN [Test Duration] - 5

                    END

                    Any test completed within the standard test day will be 0.

                     

                    Step 6: Create calculated field [Test Status]

                    IF ISNULL([End Date]) THEN 'Open'

                    ELSEIF NOT ISNULL([End Date]) THEN 'Complete'

                    END

                    See attached workbook in Tableau 10.1.

                     

                    Hope this helps.

                    Ossai

                     

                     

                    • 7. Re: Dateadd
                      Lanre Olubajo

                      Hi Ossai,

                      I will give steps 4&5 a try and get back to you, but just going by what I see, the logic makes a lot of sense. I will keep you posted.

                      • 8. Re: Dateadd
                        Okechukwu Ossai

                        Hi Olusegun,

                         

                        The fields used in the steps 4 & 5 are derived from the previous steps, so steps 4 & 5 cannot be tried in isolation. Implement the solution from the beginning and let me know how it goes.

                         

                        Ossai

                        • 9. Re: Dateadd
                          Okechukwu Ossai

                          The solution can be simplified assuming start and end dates are all weekdays and the Tableau report will not be generated on a weekend. See attached workbook.

                           

                          Step 1: Create calculated field [Test Duration]

                          MIN(

                          IF NOT ISNULL([End Date]) THEN

                              DATEDIFF('day', [Start Date], [End Date]) + 1

                              - 2 * DATEDIFF('week', [Start Date], [End Date])

                          ELSEIF ISNULL([End Date]) THEN

                              DATEDIFF('day', [Start Date], TODAY()) + 1

                              - 2 * DATEDIFF('week', [Start Date], TODAY())

                          END)

                           

                          Step 2:Create calculated field [Days Overdue]

                          IF [Test Duration] <= 5 THEN 0

                          ELSEIF [Test Duration] > 5 THEN [Test Duration] - 5

                          END

                           

                          Hope this helps.

                          Ossai

                           

                          • 10. Re: Dateadd
                            Lanre Olubajo

                            Hi Ossai,

                            This works perfectly. Thanks a lot, nice one.

                            • 11. Re: Dateadd
                              Lanre Olubajo

                              Thanks everyone for your input, This has now been answered.

                              • 12. Re: Dateadd
                                Okechukwu Ossai

                                You're welcome. Glad it helped.