Query in card building in DOMO.
Hi,
I need to create a card in DOMO similar to this,
Based on this, I choose type as "Pivot Table" in Domo with
Rows as C1 and C2
Columns as year, month
Values as Count(Sum of C3), Avg. Duration(Avg of C4), Engaged(SUM of C5), Transferred(Sum of C6)
where C1, C2, etc are different columns in dataset.
And now, card in DOMO is similar to this,
Now, the requirement is, need Avg Duration, Engaged and Transferred which are presently columns in DOMO card to be as rows(means something similar to first pic). Is this possible in anyway DOMO?
Thanks in advance!!
Answers
-
0
-
Hi @RobSomers
We have added engaged and transferred as Rows in pivot table,
but its grouping data accordingly and all these are fields are present as different columns as below.
But we are expecting all these fields(completed, engaged, transferred) in same column but in different row as below,
0 -
@PRC your data is going to need to be restructured to get to the visual you are trying to achieve. You will need a single column (i.e. category) that holds the value of 'Completed', 'Engaged',or 'Transferred' and then use that column in your pivot table row.
Data structure:
Card:
KB Article: https://domohelp.domo.com/hc/en-us/articles/360043429473-Pivot-Table
1 -
@PRC you can't take something that you built (i assume) as a spreadsheet (not an actual pivot table) in excel and then expect a 1:1 match in Domo.
In order to have one row for each metric, you'd have to have a column that contains the different values "All, "avg. duration", "transferred", "engaged"... etc. with the appropriate rows of data for each section.
the first two rows would be "all data" the last 4 would be "only rows where the status = ..."
once you get that down the metrics columns should be easy. unless they are percent of total, in which case you can either use the FIXED function
https://domohelp.domo.com/hc/en-us/articles/4408174643607-Beast-Mode-FIXED-Functions
or a window function with a CASE statement referring to the All Rows
sum(sum(case when status = 'all' then <metric>)) over ()
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"2 -
Hi @mhouston / @jaeW_at_Onyx
We have this data in BigQuery. Though complete/incomplete fields are mutually exclusive data. Transferred/Engaged are not. So, we are not able to go with restructuring of data as described above.
0 -
I'm sorry i don't understand. @PRC whta does having data in BigQuery have to do with anything?
Just pull it into domo and transform it.
It doesn't matter if the data is mutually exclusive or not. You can have Sales Data and Budget Data, that information is mutually exclusive. To present the data in a card / table, you would UNION the datasets on common fields and then use the "Activity Type" to differentiate.
Do the same here.
For Transferred / Engaged, take the set of data that applies to Transferred and UNION it to the set of data that relates to Engaged and again differentiate the data on "Activity Type"
For Total Rows, again UNION the whole set of applicable data and set the Activity Type to "Total"
Don't worry about double-counting duplicate rows. You're using a CASE statement to differentiate between the different chunks of data.
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"1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 709 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive