Pivot Table

TMonty0319
TMonty0319 Member
in Beast Mode

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.

image.png
image.png

Tagged:

Answers

  • david_cunningham
    david_cunningham Coach

    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.

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • TMonty0319
    TMonty0319 Member

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

  • david_cunningham
    david_cunningham Coach
    edited April 15

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

    image.png
    image.png
    image.png

    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.

    image.png

    Hope that this is helpful!

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • TMonty0319
    TMonty0319 Member

    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

    image.png

  • david_cunningham
    david_cunningham Coach

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

    image.png

    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.

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • TMonty0319
    TMonty0319 Member

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

Categories