Please Help - Cant get pivot table working properly

TMonty0319
TMonty0319 Member
in Beast Mode

I am having a problem with a formula. I am trying to display Yes for a facility if they have a task event status in Completed or CompleteLate status. If they have a task in any other task event status I want to Display No. In my pivot table in domo I am getting an error of multiple results encountered for same location. If there are multiple results of No and Yes I need just No to display.

image-bb32ef2847df8-95f1.png

Here is sample of my data and the Task Complete column is what my desired output in Domo Pivot table is.

image.png

I have tried a couple diff formulas and cant get this to work. Getting very frustrated.

Formula 1

Case When SUM(CASE WHEN `taskEventStatus` in ('Completed', 'CompleteLate') THEN 0 ELSE 1 END) = 0 then 'Yes' else 'No' END

Formula 2

CASE

WHEN MAX(CASE WHEN `taskEventStatus` in ('Not Started', 'Expired', 'Late', 'On Time') THEN 1 ELSE 0 END) = 1 THEN 'No' ELSE 'Yes' END

Tagged:

Best Answer

  • Chris_Wolman
    Chris_Wolman Contributor
    edited April 17 Answer ✓

    Hello,

    Beastmodes are calculated at the row level and you are trying to calculate a value over multiple rows. You will need to use a window function with a partition or a fixed by function. Here is an example using a window function.

    image.png
    image.png

    Chris

    Chris

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

Answers

  • Jones01
    Jones01 Contributor

    @TMonty0319 first thing to check would be to make sure you don't have something in your sort order that isn't being used in your pivot table.

  • Chris_Wolman
    Chris_Wolman Contributor
    edited April 17 Answer ✓

    Hello,

    Beastmodes are calculated at the row level and you are trying to calculate a value over multiple rows. You will need to use a window function with a partition or a fixed by function. Here is an example using a window function.

    image.png
    image.png

    Chris

    Chris

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

  • TMonty0319
    TMonty0319 Member

    Tried both suggestions and same result. Seems like this is possible but I dont know why has been so difficult

  • Chris_Wolman
    Chris_Wolman Contributor
    edited April 17

    There must be some other grouping on your table that is preventing the beastmode from aggregating. As @Jones01 called out. Do you have another column that is sorting or additional columns in your pivot table? If so, those either need to be aggregated, removed or included in the partition. My recommendation would be to create a new card only using those 4 columns and verify the beastmode is working.

    Chris

    ** 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. I dont know dont get it. Frustrating. Made a new card and made sure no extra sorting and everything included in partition

  • Chris_Wolman
    Chris_Wolman Contributor

    @TMonty0319 Do you have other beastmodes in the pivot table or summary number? What about null values in any of the columns?

    Chris

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

  • TMonty0319
    TMonty0319 Member

    Maybe it has to do some nulls. How would I account for nulls in formula?

  • TMonty0319
    TMonty0319 Member

    Got it working. All set. Thanks for the help!

  • Chris_Wolman
    Chris_Wolman Contributor

    @TMonty0319 great news! How did you resolve the issue? Good to know for anyone who sees this thread in the future.

    Chris

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

Categories