1 2 Previous Next 15 Replies Latest reply on Jan 30, 2013 5:21 PM by Dimitri.B

# Transform a Dimension group into a Measure format

Hello,

I would like some tips if someone has any idea.

I have created a group of a dimension (date) to affect some specific numbers to each dates.

Ex:

74,000     Jan 2011

85,000     Feb 2011

45,000     March 2011

etc.

I had to to that to avoid to touch at the database.

Then now I would like to use those numbers into a graph and apply some calculation, for that I need my numbers as a Measures.

Do you know how to transform a Group into a measure in Tableau?

Thank you!

• ###### 1. Re: Transform a Dimension group into a Measure format

If those are two different dimensions, then just right-click the dimension and choose "Convert to Measure". Is this what you were looking for?

--Shawn

EDIT: OK, re-reading it looks like you've got these coming in as strings. In that case, you're looking for this calc:

INT(LEFT([Mess],FIND([Mess]," ")))

And then convert this to a Measure. (See Attached.)

2nd EDIT: And then set the Number format on the Measure so you get the thousands separator back.

• ###### 2. Re: Transform a Dimension group into a Measure format

Well, you can't convert a group to a Measure, at least I don't see the option with a right click.

(same thing with a date)

• ###### 3. Re: Transform a Dimension group into a Measure format

See my edit above.

• ###### 4. Re: Transform a Dimension group into a Measure format

Thank you for the tip here, but for my case, see the attached (Mess Group), this is a group like that I would like to transform into a Measure.

• ###### 5. Re: Transform a Dimension group into a Measure format

Jean, you've really tied your hands by bringing these in as "groups". The only thing you can do is ungroup them:

You can't use groups in calculated fields (I'm fairly certain) and you can't change them to measures. Basically you can't use anything to "get at them", seems the only thing you can do is display them as a dimension. You need to re-think the way you'e bringing your data in. A join maybe? Custom SQL?

--Shawn

• ###### 6. Re: Transform a Dimension group into a Measure format

I see, it is what was I thought but I wanted to be sure there is no way to do that in Tableau

The ideal way should be to add few columns on the table in the DTW directly I guess, but I don't want to touch it yet since it is in PROD and it will take a couple of days to do that.

Customer SQL in tableau, I never done it before so why not, but can you add Dimensions or Measures fields, taking the data from a different table in the DTW that the one you have already imported?

Otherwise I guess the calculation with many IF will be the solution?

• ###### 7. Re: Transform a Dimension group into a Measure format

Jean, I'm pinging my friend Dimitri, because you're getting outside my knowledge zone. Hope he'll be able to guide you in the right direction.

--Shawn

• ###### 9. Re: Transform a Dimension group into a Measure format

Jean,

The large IF statement is possible but that depends on how many encodings you'll need. If there are only 10 or so possible values to encode, then built-in IF function would be practical, otherwise it might still work (I don't know if there is a limit), but will become painful to manage.

If you have an encoding table or view in the same DTW, then it is a piece of cake to either join it in Tableau's connection or create a custom SQL connection.

I need to know details to assist your better, i.e. structure of the tables or even sample data, and database type.

-Dimitri-

• ###### 10. Re: Transform a Dimension group into a Measure format

I see, yes I would like to avoid the IF management, since I have at least 24 different figures now and it will grow (1 per month since Jan 2011).

So I pull the main table from the DTW (Oracle database), which is the ledger with a filter to select only the departments I need. Then I need to create for each months 2 different fields (the total of customers and the total of work side employee).

How do I add those figures (monthly total WSE and Customer) to my Ledger table cost per fiscal month.

I hope I am enough clear, let me know

• ###### 11. Re: Transform a Dimension group into a Measure format

Where would WSE and Customer numbers come from? I assume they are not in the ledger. Are they in some other table in the same DTW?

• ###### 12. Re: Transform a Dimension group into a Measure format

Exactly, this number is stored in the DTW under the customer table.

But it's updated every month, you don't have a picture month by month.

I take the figures from an excel spreadsheet that finance build and I have to aggregate it.

• ###### 13. Re: Transform a Dimension group into a Measure format

Assuming that customer data in DTW gets overwritten every month, and you need month-by-month history of changes, I am afraid I can't offer you anything better than extracting customer details manually every month and storing them in your own spreadsheet. This could be in the same workbook as your finance data, which will allow you to do either a join connection or custom SQL. I would need a data sample if you want help with setting it up.

• ###### 14. Re: Transform a Dimension group into a Measure format

I see. Well I can give you a data sample, which one do you need? both?

Another question, how do you link an external table / spreadsheet to a DTB table connection.

I thought it was not possible?

1 2 Previous Next