Show players name based on score in domo summary number

vgupta
vgupta Member
edited March 2023 in Scheduled Reports

I have a dataset something like below. I want to show top 3 players name who have maximum score in summary number in notebook card. I want to do that using Domo beast mode. How can I do that ? I don't want to use tables.

Player score

player1 10

player2 20

player3 5

player1 20

player3 30

player4 15


I want to show result like below :

player3 | 35 player1 | 30 player2 | 20

winner runner up 1 runner up 2

Comments

  • If you use the limit rows property and set it to 3 and then sort by point descending, you can use the LISTAGG() function to list them in a summary number in order.

    LISTAGG(playername)

    to show the name and the score you try:

    LISTAGG(CONCAT(playername,' | ', score))

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • If you want to display values in a notebook card using summary numbers, then they need to be values and not text fields. I noticed all of your players could be identified by a number so I took a stab at this one. I had to use an ETL to help with this prior to creating the card. I used magic etl to alter your table to be something like this:

    You can copy and paste this into your etl to see how I did it:

    {"contentType":"domo/dataflow-actions","data":[{"name":"Dojo vgupta","id":"92c4f7a9-7617-4e4d-bc0c-01cd2d9db890","type":"LoadFromVault","gui":{"x":36,"y":180,"color":3238043,"colorSource":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"86eb1e4b-5fa8-4dfd-b5ce-1167d5288d5e","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null,"versionWindow":null},{"name":"Total Score","id":"c19f4ca6-a0e0-42d4-8adf-64ec88f08508","type":"GroupBy","gui":{"x":168,"y":48,"color":null,"colorSource":null},"dependsOn":["92c4f7a9-7617-4e4d-bc0c-01cd2d9db890"],"removeByDefault":false,"notes":[],"addLineNumber":false,"lineNumberFieldname":null,"giveBackRow":false,"allRows":false,"groups":[{"name":"Player"}],"partitionedAggregation":false,"fields":[{"name":"TotalScore","source":"Score","type":"SUM","valuefield":null}]},{"name":"rank","id":"2ad36901-9f4a-4b41-b8de-99ceba909b15","type":"WindowAction","gui":{"x":276,"y":48,"color":null,"colorSource":null},"dependsOn":["c19f4ca6-a0e0-42d4-8adf-64ec88f08508"],"removeByDefault":false,"notes":[],"partitionedAggregation":false,"additions":[{"name":"Rank","operation":{"type":"RANKING","operationType":"RANK"}}],"orderRules":[{"column":"TotalScore","caseSensitive":false,"ascending":false}],"groupRules":[]},{"name":"result","id":"5d2dea58-d0d8-4001-a86b-2f01df0308d6","type":"ExpressionEvaluator","gui":{"x":408,"y":48,"color":null,"colorSource":null},"dependsOn":["2ad36901-9f4a-4b41-b8de-99ceba909b15"],"removeByDefault":false,"notes":[],"expressions":[{"expression":"Case when `Rank`=1 then 'winner' when `Rank`=2 then 'runner up 1' when `Rank`=3 then 'runner up 2' else 'participation trophy' end","fieldName":"Result","settings":null}]},{"name":"Join Data","id":"b4d5e0f1-0037-45d7-a1f1-2e8295a1e2ab","type":"MergeJoin","gui":{"x":528,"y":180,"color":null,"colorSource":null},"dependsOn":["92c4f7a9-7617-4e4d-bc0c-01cd2d9db890","5d2dea58-d0d8-4001-a86b-2f01df0308d6"],"removeByDefault":false,"notes":[],"joinType":"INNER","step1":"92c4f7a9-7617-4e4d-bc0c-01cd2d9db890","step2":"5d2dea58-d0d8-4001-a86b-2f01df0308d6","keys1":["Player"],"keys2":["Player"],"schemaModification1":[],"schemaModification2":[{"name":"Player","rename":"","remove":true}],"partitioningInputId":""},{"name":"Add Formula","id":"ff7d2290-33a3-4108-baf5-ad8d8a685566","type":"ExpressionEvaluator","gui":{"x":636,"y":180,"color":null,"colorSource":null},"dependsOn":["b4d5e0f1-0037-45d7-a1f1-2e8295a1e2ab"],"removeByDefault":false,"notes":[],"expressions":[{"expression":"trim(leading 'player' from `Player`)","fieldName":"Player number","settings":null}]},{"name":"Alter Columns","id":"bf2e1f9f-a71b-4527-9e1d-442033855ed7","type":"Metadata","gui":{"x":744,"y":180,"color":null,"colorSource":null},"dependsOn":["ff7d2290-33a3-4108-baf5-ad8d8a685566"],"removeByDefault":false,"notes":[],"fields":[{"name":"Player number","rename":null,"type":"LONG","dateFormat":null,"settings":null,"remove":false}]},{"name":"ETL vgupta","id":"4a34775f-41a2-488a-b879-acefadf6f14d","type":"PublishToVault","gui":{"x":924,"y":180,"color":null,"colorSource":null},"dependsOn":["bf2e1f9f-a71b-4527-9e1d-442033855ed7"],"removeByDefault":false,"notes":[],"dataSource":{"guid":"5e12f8a1-f879-44bd-83f5-f708ed9d7671","type":"DataFlow","name":"ETL vgupta","cloudId":"domo"},"versionChainType":"REPLACE","partitionIdColumns":[],"upsertColumns":[]}]}
    

    You can now filter your summary number based on the result value, like this:

    Same for the Total Score

    This lets you end up with something like this:

    You can also use a calculated field to create a long summary number on a visualization card:


    CONCAT(
      'Winner: ',max(case when `Result`='winner' then `Player`end), ' | ',MAX(case when `Result`='winner' then `TotalScore` end)
      ,' - Runner Up 1: ',max(case when `Result`='runner up 1' then `Player`end), ' | ',MAX(case when `Result`='runner up 1' then `TotalScore` end)
      ,' - Runner Up 2: ',max(case when `Result`='runner up 2' then `Player`end), ' | ',MAX(case when `Result`='runner up 2' then `TotalScore` end)
      )
    

    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • @MarkSnodgrass and @ST_-Superman-_ have super creative ideas... but i strongly recommend calculating this in ETL... your card won't respond to page filters

    and if users aren't using filters then you're probablly creating single use datasets which isn't ... ideal.

    consider building a table card with a LIMIT=3 (down in the data table

    then just calculate rank() order by sum(amount) desc.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"