How do I get the following aggregation setup in domo?

rahulrampa

I have a dataset as follows: and I want the aggregations as shown below (number sum is just the sum aggregation of column 'number'

the first table is the actual data and the next three tables are the aggregations I want in the same sheet (i.e. the user should be able to add/remove the columns on their own and see those values).

I have tried using fixed level of details but couldn't quite get it to work.

DavidChurchman

First, how to get those aggregations (no formula needed):

You could set up a table card with two columns, id and number . If you select "sum" for the aggregation for that field, it will show the sum per id. Then you could have a second table card with list and number and a third with just number. To match your question, I said to use a table card, but this is the same logic for most of the viz cards (bar graphs, etc.)

Second, if instead of three separate cards, you want a single card where a user can switch between the different aggregations:

Set up a variable with three options, 'id', 'level', 'total'. Then create a beast mode like:

CASE

WHEN variable = 'id' then id

when variable ='list' then list

when variable='total' then 'Total'

END

Note that the final 'Total' is just an arbitrary constant, it could be anything.

Then you make a table with that Beastmode you created and your number column, and you can add a control to your page using the variable you created.

Here's the overview of variables to reference: https://domo-support.domo.com/s/article/7903767835031?language=en_US

DavidChurchman

First, how to get those aggregations (no formula needed):

You could set up a table card with two columns, id and number . If you select "sum" for the aggregation for that field, it will show the sum per id. Then you could have a second table card with list and number and a third with just number. To match your question, I said to use a table card, but this is the same logic for most of the viz cards (bar graphs, etc.)

Second, if instead of three separate cards, you want a single card where a user can switch between the different aggregations:

Set up a variable with three options, 'id', 'level', 'total'. Then create a beast mode like:

CASE

WHEN variable = 'id' then id

when variable ='list' then list

when variable='total' then 'Total'

END

Note that the final 'Total' is just an arbitrary constant, it could be anything.

Then you make a table with that Beastmode you created and your number column, and you can add a control to your page using the variable you created.

Here's the overview of variables to reference: https://domo-support.domo.com/s/article/7903767835031?language=en_US

rahulrampa

But if I select the 'id' column, I want to sum for user-1 to be 5 and not 10(essentially the max of the values across both the lists for a user). If I just take sum aggregation directly, the result with be 10 for the first user and 20 for the second user but I have 5 and 10 respectively.

DavidChurchman

That's a bit different. It's not a simple sum, but the sum of the max value by user. That would require a fixed function ( https://domo-support.domo.com/s/article/4408174643607?language=en_US ) that could look like this:

sum(max(number) fixed(by id))

You could still either make 3 cards or create a variable that allows a user to flip between the 3 levels of aggregation.