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

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.

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

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

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

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

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

Thanks,

Ritesh

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

Thanks,

Ritesh

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

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.

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

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