How do I get the following aggregation setup in domo?

rahulrampa
rahulrampa Member
in Beast Mode

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

image.png

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.

Tagged:

Best Answer

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

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

    image.png

    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

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Answers

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

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

    image.png

    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

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • rahulrampa
    rahulrampa Member

    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
    DavidChurchman Coach

    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.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Categories