Modify formula

Options
TMonty0319
TMonty0319 Member
in Beast Mode

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.

Tagged:

Answers

  • GrantSmith
    GrantSmith Coach
    Options
     
    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • TMonty0319
    TMonty0319 Member
    Options

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

    image.png

Categories