Query in card building in DOMO.

PRC
PRC Member
edited September 2022 in Charting

Hi,

I need to create a card in DOMO similar to this,

image.png


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,

image.png


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

  • PRC
    PRC Member


  • RobSomers
    RobSomers Coach

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

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • PRC
    PRC Member

    Hi @RobSomers

    We have added engaged and transferred as Rows in pivot table,

    image.png

    but its grouping data accordingly and all these are fields are present as different columns as below.

    image.png


    But we are expecting all these fields(completed, engaged, transferred) in same column but in different row as below,

    image.png


  • mhouston
    mhouston Contributor

    @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:

    image.png


    Card:

    image.png


    KB Article: https://domohelp.domo.com/hc/en-us/articles/360043429473-Pivot-Table

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    edited September 2022

    @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"
  • PRC
    PRC Member

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

    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"

Categories