Issue with the logic in beast mode calculation

Yadhuveer

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.

MichelleH

@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.

nmizzell

You need to restructure your dataset in ETL instead of using a Beastmode. I suggest you take the following steps:

1. 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.
2. 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.

MichelleH

@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.

nmizzell

You need to restructure your dataset in ETL instead of using a Beastmode. I suggest you take the following steps:

1. 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.
2. 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.