Please Help - Cant get pivot table working properly

TMonty0319

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.

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

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

Chris_Wolman

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.

Chris

Jones01

@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

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.

Chris

TMonty0319

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

Chris_Wolman

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.

TMonty0319

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

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

TMonty0319

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

TMonty0319

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

Chris_Wolman

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