Data Aggregation issue in the Beast Mode Calculation while using Sub-Total and Header Row.
While we use the CASE statements the Aggregation in DOMO first performs SUM and then looks at the condition inside the CASE statement. This means that the following two conditions will provide similar results on the row level, but when the data is aggregated the 2nd condition will only look at the bigger value post SUM() and shall provide incorrect results.
WHEN Col1>0 AND (Col1>Col2) THEN Col1
WHEN SUM(Col1)>0 AND (SUM(Col1)>SUM(Col2)) THEN SUM(Col1)
Let's suppose that we look at the first row with two columns, Val1 and Val2. Val1 has 4, Val2 has 50. In this case, Val2 is bigger, and so Val2 will be supplied for that row. So on and so forth. Let's suppose that for every row after that, Val1 is 2, and Val2 is 1, so we end up with a set of rows with Val2 on the first row, and Val1 on all other rows. Then let's sum up all of the values for each row. Let's say we had 5 rows. So we would end up with 50, 2, 2, 2, 2. The sum is then equal to 58.
Cool, so now let's suppose that INSTEAD, we Sum up everything and THEN we apply the beast mode calculation. This means that we sum up everything in Val1, which will be 4 + 2 + 2 + 2 + 2 = 12. Then we sum up Val2 which will be 50 + 1 + 1 + 1 + 1 = 54. Now we do the beast mode. Is Val1 or Val2 bigger? Val2 is, so we will return that, so the display 54. Notice that this sum is 54, instead of 58, which was our sum result with the previous method.
Always wrap your beast mode in a SUM(CASE STATEMENT) to get the correct value at both row and aggregate levels.
- 7.3K All Categories
- 13 Getting Started in the Community
- 134 Beastmode & Analytics
- 1.8K Data Platform & Data Science
- 52 Domo Everywhere
- 2K Charting
- 994 Ideas Exchange
- 894 Connectors
- 236 Workbench
- 342 APIs
- 76 Apps
- 18 Governance & Productivity
- 233 Use Cases & Best Practices
- 49 News
- 473 Onboarding
- 570 日本支部