What is the easiest way to visualise datasets structured like a matrix?

Brenton
Brenton Member
in Archive

Howdy. Domo seems to really struggle visualising datasets structured like a matrix. For example, dates in row 1, locations in Column A.... Our Marketing department has purchased Tableau and it doesn't have any issues visualising these types of datasets. They are taunting us Domo users and I need answers! Thanks!

Comments

  • creed
    creed Domo Employee

    Hi@Brenton,

     

    Are you looking to display the data in a matrix visualization type, or is the data currently in a matrix format and you're trying to visualise it as something like a bar chart?

     

    If you would like to display it as a matrix then the Sumo card is perfect for that. You can set it up as a pivot table so the dates would be displayed across the columns on the first row, and the locations would be displayed down the rows in column A, with the amounts being displayed at the intersection of dates and locations. Here is an example:

     

    Capture.JPG

     

    If the data is currently in a pivoted matrix format and you want to build a card off of that then you can use MySQL or Magic ETL to unpivot the columns into rows, and then build your visualization off of the new, normalized dataset.

     

    Please let me know if you have any questions with either of these approaches.

     

    Thank you,

    Creed


    **Say "Thanks" by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • kshah008
    kshah008 Contributor

    @Brenton, did creed's reply help answer your question? 

  • Brenton
    Brenton Member

    Hi Creed. Thanks so much for taking the time to reply. I wasn't aware you replied. I would have replied much sooner!

     

    I am looking to take data currently in a matrix format and visualise it as something like a barchart.

     

    I do not have MySQL skills so will need to use the MagicETL approach. What do you mean by up-pivot columns?

  • creed
    creed Domo Employee

    No problem Brenton! With the data in a matrix format we will be able to create a bar chart with the help of a few simple steps in a MagicETL dataflow. "Unpivot" simply means to turn the columns into rows. You can also call this operation "transpose" or "collapse". The goal is to take a datset like this (pivoted with values spanning many columns):

     

    1.png

     

    ...and transform it into something like this (unpivoted with values for those columns now spanning rows):

     

    2.png

     

    Having the data in this format allows us to build standard cards like this:

     

    3.png

     

    Here are the steps using MagicETL.  We will be adding an Input Dataset, Collapse Columns transformation, Set Column Type transformation (optional), and finally an Output Dataset.

     

    Step 1: Create a Magic ETL dataflow and bring in the source dataset.

    7.png

     

    Step 2: Add the "Collapse Columns" transformation in the "Edit Columns" section. In the transformation settings it asks you for the column names for the category and value columns that will be transposed into rows. In my example I am naming this new column "Date" to hold the dates that were previously populated in each of the columns, and "Amount" for the amounts that were in the intersection of each location and month. In the next section of this setting you will enter the list of columns that you wish to transpose.

     

    4.png

     

    TIP - you do not need to manually enter each of the columns. You can select the "ADD ALL COLUMNS" option and it will automatically bring in each column and give it a new label. It will also bring in the location column so you will want to remove that since we do not need to transpose that column. It will simply be copied down for each new row that we create.

    5.png

     

    Step 2a: You may need to tell MagicETL that the new columns are a specific data type (i.e. Decimal for a numeric value, or Date for a date value). This can be done using the "Set Column Type" transformation in the "Edit Columns" section. You can check what the data type is currently set to by clicking on the "Add Column" option on the Set Column Type transformation settings.

     

    10.png

    In this example it thought that "Date" was text (should be a date) and "Amount" was a whole number (should be decimal). If this is not set correctly then the card won't display the information correctly, so it is important to verify that the correct data types are set.

     

    8.png

     

    Step 3: Once you have added the transforms then you can select an Output Dataset and run the dataflow. It will produce the unpivoted dataset so you can then build a card.

     

    Those are the steps, I know it's a lot of information (and screenshot overload!) but once you have the general idea it isn't too bad. The only caveat is if you add new columns to your source dataset then you will also need to update the Collapse Columns transformation. It will not automatically detect new columns to transpose. If a dynamic/automatic solution is needed then MySQL will likely be needed. 

     

    I hope this answers your question, but if not- please let me know and I'll clarify where needed. 

     

    Thank you,

    Creed


    **Say "Thanks" by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
This discussion has been closed.

Categories