Modify formula

TMonty0319 · 2024-04-12T16:06:26+00:00

There was an error rendering this rich post.

I need to modify a formula.

CASE WHEN taskEventStatus = 'Completed' OR taskEventStatus = 'CompleteLate' THEN 'Yes' ELSE 'No' END

I have plants with event status. Each plant can have 1-4 tasks to complete a day. I need formula so if not all tasks are' complete' or 'completedlate' status (yes) then No. If all tasks are either completed or completedlate then need value of yes.

Right now, with my formula I get multiple values returned in some cases. I get a yes and a no if one of tasks is not complete or completelate. I need to always assign a No when this happens and it wont be yes unless all tasks meet the yes criteria.

Find more posts tagged with

Beast Mode

Accepted answers

All comments

GrantSmith

MIN(MIN(CASE WHEN taskEventStatus = 'Completed' OR 
taskEventStatus = 'CompleteLate' THEN 'Yes' ELSE 'No' END)) OVER (PARTITION BY `PlantID`,`Date`)

You can use a window function to return the minimum value (No if any of them are No) for each date and plant ID.

TMonty0319

Tried this out but still not working. Still getting error in my pivot table chart saying multiple results for same location.