Please Help - Cant get pivot table working properly
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
Best 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.
Chris
Chris
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**0
Answers
-
@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.
0 -
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
Chris
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**0 -
Tried both suggestions and same result. Seems like this is possible but I dont know why has been so difficult
0 -
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! ✔️**0 -
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
0 -
@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! ✔️**0 -
Maybe it has to do some nulls. How would I account for nulls in formula?
0 -
Got it working. All set. Thanks for the help!
1 -
@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! ✔️**0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive