left([whatever your dimension name is],5)
Change the field type to geographic role, zip
You do not want to change it to integer as this will change any zips with a leading zero to a 4 digit zip.It stays as a dimension by the way - you would never create a bar chart with the height of the bars being the average of the zips for example - zip is a dimension like gender, department, country...
Hi Alex. Thanks for this response. I'm sorry to ask such a newbie question, but how do you create a calculated field from a dimension? When I try to create a calculated field the window only displays measures rather than dimensions.
Calculated fields can only be created with measures when using Cubes. This information is already defined within the cube. The following KB article provides further information between relational data sources and cubes:
Hmm, interesting - it seems however you can still write this calculation. From the page:
When connected to a cube, many concepts related to string manipulation are not available in the calculation editor. This is because cube dimensions do not show up as available fields in the editor
Alternative: You can write MDX calculated members directly inside of Tableau to manipulate dimensional values, e.g.
LEFT([Product].[Product Categories].DataMember.MemberValue,LEN([Product].[Product Categories].DataMember.MemberValue)-5).
Note: This is preferred to perform these types of manipulations directly in your cube prior to analysis.
I don't quite see how you map the [product] and [product categories] map to your cube structure, but it looks like we can do this, maybe something like the following:
Thanks again for your help. I've been trying to create a calculated member, but I have no background in writing MDX. I followed the process for creating a calculated member, but when I use your query it appears that it doesn't create a new member in the dimension heirarchy. I've attached a screenshot that shows my screen. I've already saved the calculated member and re-opened it.
It may turn out, as Tracy noted, that I will need to make changes at the cube level rather than at the reporting level, but if I can make this work it would be a preferred approach. Any further ideas?
Tableau OLAP Screen Shot.JPG 284.3 KB
Beyond my knowledge as well - I don't have any cubes I can try it on either.
It probably doesn't help that much, but I had the exact same issue wanting to extract a substring from an entity considered by a cube to be a dimension. It the end, I just dumped the whole data set into Excel, and read it back in as a relational database. That puts a stop to silly strait jackets.
Cubes are a 1960's throwback to when computing resources were expensive/scarce, query roll ups were pre-determined and people wanted granular control over every detail of what fields you were and were not authorised to see. Mix that in with a cynical computer science professor who delighted sending users around ever more complex knots. Just say no (dismounts soap box).
You sire can stay on that soap box!
Using my first production cube in Tableau and it's eye-opening. I have been using Tableau on relational sources for a few years and using a cube is like going back in time.