How to modify Tableau formula to set specific date and activity to 0?

Question

I’m working on a Tableau Desktop formula and need help adjusting it.

I need to modify it so that the entry with Date: 7/23/2024, activity_category: TimeOff, activity_name: LOA UTO, and activity_duration: 30 is set to 0.

My Date column could have one single date or many of the same date listing down on rows for each employee, example:

Employee        Date        activity_category   activity_name   activity_duration   Credits Used MAX
Gracie        7/15/2024             TimeOff           LOA UTO             30                       1
Gracie        7/16/2024             TimeOff           LOA UTO             30                       1
Gracie        7/23/2024               Break           Break               15                       0
Gracie        7/23/2024               Meal            Meal                30                       0 
Gracie        7/23/2024              TimeOff          LOA UTO             30                       1
Gracie        7/23/2024              On Queue         On Queue            120                      0

Here’s a sample of my data for reference:

Employee             Date       activity_category   activity_name   activity_duration   Credits Used MAX
Gracie             7/23/2024      TimeOff             LOA UTO              30                   1

I created a Tableau desktop formula, and I would like to have the last column titled Credits Used MAX turn from 1 to 0 only if the employee has activities with the same Date. If there is only one individual Date for the employee this means he/she were out the entire day and should receive a 1, which in this example it is correct.

IF [activity_category] = 'OnQueueWork' AND [activity_name (Custom SQL Query2)] IN ('OnQueueWork', 'On Queue') AND INT([activity_duration]) >= 120 THEN 0 ELSEIF [activity_category] = 'TimeOff' AND INT([activity_duration]) >= 120 THEN 1 ELSEIF { FIXED [Date] : COUNTD([Date]) } = 1 THEN IF [activity_category] = 'TimeOff' AND [activity_name (Custom SQL Query2)] = 'LOA UTO' AND INT([activity_duration]) = 30 THEN 1 ELSEIF [activity_category] = 'OnQueueWork' AND [activity_name (Custom SQL Query2)] = 'Overtime' AND INT([activity_duration]) = 30 THEN 1 ELSE 0 END ELSEIF { FIXED [Date], [activity_category], [activity_name (Custom SQL Query2)], [activity_duration] : COUNT([Date]) } > 1 THEN IF [activity_category] = 'TimeOff' AND [activity_name (Custom SQL Query2)] = 'LOA UTO' AND INT([activity_duration]) <= 30 THEN 0 ELSE 0 END ELSE 0 END

Answer 1

Try this:

IF [activity_category] = 'OnQueueWork' 
    AND [activity_name (Custom SQL Query2)] IN ('OnQueueWork', 'On Queue') 
    AND INT([activity_duration]) >= 120 
THEN 
    0 
ELSEIF [activity_category] = 'TimeOff' 
    AND INT([activity_duration]) >= 120 
THEN 
    1 
ELSEIF { FIXED [Employee], [Date] : COUNTD([activity_category]) } = 1 
    AND [activity_category] = 'TimeOff' 
    AND [activity_name (Custom SQL Query2)] = 'LOA UTO' 
    AND INT([activity_duration]) = 30 
THEN 
    1 
ELSEIF { FIXED [Employee], [Date] : COUNTD([activity_category]) } > 1 
    AND [activity_category] = 'TimeOff' 
    AND [activity_name (Custom SQL Query2)] = 'LOA UTO' 
    AND INT([activity_duration]) = 30 
THEN 
    0 
ELSE 
    0 
END

EDIT

Or since so many conditions lead to the same result, you could try keeping only the conditions that lead to the value 1, which should be equivalent to the original answer, just slightly simpler to work with.

IF [activity_category] = 'TimeOff' AND
   ( INT([activity_duration]) >= 120 OR
     ( INT([activity_duration]) = 30 AND
       [activity_name (Custom SQL Query2)] = 'LOA UTO' AND
       { FIXED [Employee], [Date] : COUNTD([activity_category]) } = 1 ))
THEN 
    1 
ELSE 
    0 
END