3 Replies Latest reply on Nov 8, 2018 1:29 PM by Ken Flerlage

# Multiple Dimensions or Multiple Metrics?

WORKBOOK ATTACHED

I’m trying to format my data (or give our data team instructions on how it needs to be modified) in order to use it to do time series financial analysis.

Currently, when I connect to our DB, all the metrics (example: assets, loans, etc) are in one metric called “value” and split by dimensions: cycle (date), institution, and name. This makes it difficult to utilize separately. However, I’m not sure how to modify it (prior to Tableau in SQL or Prep, for instance) – or what best practice is. Its not the pivot that I thought it would be and I am stumped and hope someone has ideas!

Attaching a workbook. When I download the same data from the front end of our current software, it comes through with the metrics as individual metrics – I believe this is what I want but would like to be certain that the hive mind agrees!!  Do I need to change anything or should I just work with this as-is? We have a lot more descriptive data about each metric I'll want to add (category, different date formats, etc)

VERSUS

• ###### 1. Re: Multiple Dimensions or Multiple Metrics?

This is definitely a bit strange of a data structure. There are situations where I definitely like this "tall and skinny" structure, particularly with survey data where there may be lots of questions with answers all on the same scale (such as 1-10). In that case, it makes a lot of sense. But, in your case, the values seem to be totally independent of each other, so my gut tells me you'd be much better off with these as separate measures. Of course, I don't know the details of your specific use case, so I can't be 100% sure of that.

What you need to do is pivot those rows to columns. Unfortunately, that's not easy to do in Tableau--pivoting from columns to rows is doable, but the opposite is not so much. That being said, there are a few options available to you:

1) Pivot it before getting to Tableau. This could be done using an ETL tool or even custom SQL.

2) Pivot in Tableau Prep. This is a bit tricky, but Joshua Milligan has written a blog on how to do it: https://vizpainter.com/unpivot-in-maestro-rows-to-columns/

3) Create calculated measures. Basically, you're just doing If statements to create measures for the specific name. For example:

\$ Total Assets

IF [Name]="\$ Total Assets" THEN

[Value]

END

See attached workbook for an example of # 3 (see the New tab).

• ###### 2. Re: Multiple Dimensions or Multiple Metrics?

THANK you! This is perfect. I really appreciate it!

• ###### 3. Re: Multiple Dimensions or Multiple Metrics?

Any time!!