Query in card building in DOMO.

PRC

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!!

PRC

RobSomers

@PRC Instead of placing Avg. Duration/Engaged/Transferred as columns or values, put them in the Rows section of the pivot table in Analyzer.

PRC

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,

mhouston

@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

jaeW_at_Onyx

@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 ()

PRC

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.

jaeW_at_Onyx

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.