-
1. Re: Transform a Dimension group into a Measure format
Shawn Wallwork Jan 29, 2013 3:56 PM (in response to Jean Francois Carton)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.
-
Parse the Mess.twbx.zip 7.9 KB
-
-
2. Re: Transform a Dimension group into a Measure format
Jean Francois Carton Jan 29, 2013 3:47 PM (in response to Shawn Wallwork)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
Shawn Wallwork Jan 29, 2013 3:54 PM (in response to Jean Francois Carton)See my edit above.
-
4. Re: Transform a Dimension group into a Measure format
Jean Francois Carton Jan 29, 2013 4:00 PM (in response to Shawn Wallwork)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.
-
Parse the Mess.twbx.zip 8.1 KB
-
-
5. Re: Transform a Dimension group into a Measure format
Shawn Wallwork Jan 29, 2013 4:13 PM (in response to Jean Francois Carton)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
Jean Francois Carton Jan 29, 2013 4:29 PM (in response to Shawn Wallwork)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
Shawn Wallwork Jan 29, 2013 4:43 PM (in response to Jean Francois Carton)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
-
8. Re: Transform a Dimension group into a Measure format
Jean Francois Carton Jan 29, 2013 4:52 PM (in response to Shawn Wallwork)I appreciate your help, and looking forward Dimitri's advice
-
9. Re: Transform a Dimension group into a Measure format
Dimitri.B Jan 29, 2013 5:01 PM (in response to Shawn Wallwork)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
Jean Francois Carton Jan 29, 2013 5:10 PM (in response to Dimitri.B)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
Dimitri.B Jan 29, 2013 5:16 PM (in response to Jean Francois Carton)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
Jean Francois Carton Jan 29, 2013 5:58 PM (in response to Dimitri.B)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
Dimitri.B Jan 29, 2013 8:30 PM (in response to Jean Francois Carton)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
Jean Francois Carton Jan 30, 2013 10:07 AM (in response to Dimitri.B)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?