Issue with the logic in beast mode calculation
Hi,
We are facing an issue with the domo beast mode calculation logic. Below is the problem along with the logic -
If the values of column A,B,C,D,E, Quantities, part no 1, part no 2 are same and the count of value for these columns (A-part no 1, A_part no 2, M_part no 1, M_part no 2, E_part no 1, E part no2) is greater than 1 then we need to take the average of that quantity. For example
When we are using below mentioned logic then we are getting average of quantities on a line chart i.e. 9000 (y axis - Quantities, x axis -D) for the above image values.
But when we are adding one more record having different part no 1 or part no 2 to the above records then we are getting the summation of all the quantities rather than taking the average of above and summation of the quantity of new record.
Expected sum of quantities on a line chart 14000 + avg(9000 + 9000) = 23000
but we are getting the sum of all the quantities 14000 + 9000 + 9000 = 32000
We have used the below logic
CASE
WHEN COUNT(DISTINCT CONCAT(A
, B
, C
, D
, E
,Quantities
,part no 1
, part no 2
))=1 AND
COUNT(DISTINCT CONCAT(A_part no 1
, A_part no 2
, M_part no 1
, M_part no 2
, E_part no 1, E_part no 2))>1
THEN AVG(Quantities
)
ELSE SUM(Quantities
)
END
If we are adding more records of different part no. than it is taking sum of all the quantities rather than average for the duplicated quantities along with summation of remaining.
Best Answers
-
@Yadhuveer It sounds like you will need to take the average quantity in a dataflow first. You can do this using a Group by tile in MagicETL and grouping by
A
,B
,C
,D
,E
,Quantities
,part no 1
,part no 2,A_part no 1
,A_part no 2
,M_part no 1
,M_part no 2, E_part no 1, and E_part no 2
. Within the same tile, take the average of the quantity.Then when you create a card off of the output dataset you only need to take a simple sum.
0 -
You need to restructure your dataset in ETL instead of using a Beastmode. I suggest you take the following steps:
- Create a 'Part Identifier' Column that is a combination of the A,B,C,D Columns. This column will represent the unique identifiers that you want to take the average over. You may use a CONCAT() function to combine the text in the columns to create unique IDs.
- Group your data on the 'Part ID' Column. In you Group by clause, create your new aggregation column 'Average Quantity' that takes the average of the "Quantities' Column.
0
Answers
-
@Yadhuveer It sounds like you will need to take the average quantity in a dataflow first. You can do this using a Group by tile in MagicETL and grouping by
A
,B
,C
,D
,E
,Quantities
,part no 1
,part no 2,A_part no 1
,A_part no 2
,M_part no 1
,M_part no 2, E_part no 1, and E_part no 2
. Within the same tile, take the average of the quantity.Then when you create a card off of the output dataset you only need to take a simple sum.
0 -
You need to restructure your dataset in ETL instead of using a Beastmode. I suggest you take the following steps:
- Create a 'Part Identifier' Column that is a combination of the A,B,C,D Columns. This column will represent the unique identifiers that you want to take the average over. You may use a CONCAT() function to combine the text in the columns to create unique IDs.
- Group your data on the 'Part ID' Column. In you Group by clause, create your new aggregation column 'Average Quantity' that takes the average of the "Quantities' Column.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive