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

Brenton

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!

creed

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:

 

 

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

kshah008

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

Brenton

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

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

 

 

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

 

 

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

 

 

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.

 

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.

 

 

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.

 

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.

 

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.

 

 

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