Pivot Table

TMonty0319

I have data with plants and tasks. Each plant has a few daily tasks assigned to them. If the status of a task is complete or completelate the task complete column needs to be yes. If it is any other status I need No displayed. I need to aggregate at Plant level so unless all of the tasks meet Yes criteria I want No displayed. Here is my beast mode formula

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

I need to display this in pivot table. Can someone help me adjust this beast mode formula. Or do i need to do it in ETL? Where these **** are I need them to be No because they have a No and Yes for tasks. When all tasks meet Yes criteria then Yes.

david_cunningham

I would suggest doing a beast mode as follows

CASE WHEN taskEventStatus = 'Completed' OR taskEventStatus = 'CompleteLate' THEN 0 ELSE 1 END

Then nest that within another case statement

CASE WHEN

SUM(CASE WHEN taskEventStatus = 'Completed' OR taskEventStatus = 'CompleteLate' THEN 0 ELSE 1 END) = 0 then 'Yes' else 'No'

end

The logic is that if the total sum = 0 then you know all records in that group are Yes, if there is a single No, then the sum will be > 1.

TMonty0319

Thanks. Tried this and still getting same error with multiple results

david_cunningham

@TMonty0319 Here is a more detailed example. You can see below that the following outcomes are expected. C=Complete NC=NotComplete

The above output is a pivot table.

For the sake of clarity, below is an example where I replicate your error. You can see that if I don't use the beast mode to aggregate, I can replicate the same error you're running into. You need to make sure you're aggregating in the beast mode so that you don't have conflicting outcomes tied to the same ID.

Hope that this is helpful!

TMonty0319

Yeah I just want the output to look like the below table but I want ID 1 to say complete since all the ones with that ID are complete or complete late, and ID 2 should be Not complete since there is one Not complete with that ID. I want one line for each ID like the table below and to group them by Yes (complete or not complete) and No otherwise

david_cunningham

@TMonty0319 I'm confused. It sounds like the output you're seeking is the exact output that I showed in my post?

Let me know if there is something I'm missing about your request, or if there is a particular part of implementing my proposed solution that you're getting stuck on. I'm happy to troubleshoot with you.

TMonty0319

Tried this exact formula with my data and still having issue. Ill keep trying. Thanks