Joe, tried your suggestion, one row per data item, the cross tab still does not allow a different set of columns, the hide feature hides column for all items in the group. I want to have a different set of cross tab columns per group. Tableau looks like it has reached it's limitation for this report.
With the data reshaped, you just need a calculated field, like:
[group name]="blah" OR ([group name]="skiberdee" AND [Measure]="Amount")
and keep only when True.
If you would like to see an example, please provide a packaged workbook. Earlier you only provided a .twb, please provide a .twbx
I doubt you have reached Tableau's limits. :)
Joe, yes I have used this approach, [group name] + ' ' + [Field] and then filtering out those that are not required. However this does mean that I need to unpivot the data to create a column [Field] that I can access programmatically. Given the size and complexity of my dataset (> 100 gig) this is not something that is trivial. I am sure that this would work. Thanks for your help.
I guess I was hoping for something more elegant :-)
Since reshaping does not fit with your constraints, you could also use a data blend. If you can provide a packaged workbook, an example can be created for you.
Do you have any other constraints, or what interaction do you want to allow for?
Joe, seems my firewall doesn't like the upload feature on this forum, get HTTP 0 error uploading files using the File-attachments feature. Here is the data that goes with the original workbook. Perhaps if you shoot me an email I can send over the packaged workbook.
group name group group name security name Amount Price Delta Gamma
ABC blah AAA 1000 123 -12.0123 0.22
ABC blah BBB 500 234 -13.455 1.22
ABC blah CCC 1000 123 -12.0123 0.22
ABC skiberdee DDD 1000 123 -12.0123 0.22
ABC skiberdee AAA 1000 123 -12.0123 0.22
ABC skiberdee BBB 1000 123 -12.0123 0.22
EFG Moo Moo AAA 1000 123 -12.0123 0.22
EFG Moo Moo EEE 500 234 -13.455 1.22
EFG Moo Moo ZZZ 1000 123 -12.0123 0.22
EFG skalath DDD 1000 123 -12.0123 0.22
EFG skalath GGG 1000 123 -12.0123 0.22
EFG skalath BBB 1000 123 -12.0123 0.22
attached is a basic example, there are lots of other options as well, like formatting each column of values differently, and table calcs for joining at one level of aggregation for filtering purposes, and aggregating at another for display.
Since your data set over 100GB, I would recommend creating an extract for the custom SQL data connection.
Be aware, there are many other options and methods if this does not fit with your constraints.
table_data_blend.twbx 8.4 KB
Joe, nice example, however the union you use to get the unpivot is not really something that could be deployed. I have hundreds of measures and this SQL would become unusable over time. I think that I have to create another ETL that does the unpivot into a table so that I can then read this data out, I can then access a [MeasureName] list in a calculated field. Thanks for your help and original suggestions.
I agree, with the added constraint of lots of additional measures, the custom SQL option for reshaping may no longer be viable.
With this additional information in combination with your previous comments, you may run into issues with this approach, particularly the speed of the refresh.
Maybe a different angle of approach or a different display of the data would enable the results you are looking for.
Generally, it is my opinion that if you just want to display a crosstab of numbers, Tableau may not be the optimal solution, for precisely the difficulties you are dealing with. All these suggestions I offer feel like a kludge, a workaround of a limitation, because I don't think Tableau was designed with the primary intent to create the view you are looking for. I believe the focus of Tableau is for the visual representation of quantitative data, and the crosstab display of numbers is more of an also can ability.
Joe, we have now re-shaped with a normalised view to achieve the [Fields] as discussed. However the numeric data that is represented by this grouping needs to be formatted differently depending on the [Field], some are percentages with 2 dpls and others are integers. Do you know if tableau can conditionally format or handle separate formatting even though we have a single measure? or have we hit a limitation with Tableau here?
If you keep asking "have we hit a limitation with Tableau here?" I may stop replying. In a previous comment in this thread, I said "like formatting each column of values differently" when I posted a workbook above.
Tableau is lacking a FORMAT() function, and until Tableau adds it, you have a few options
For taking a number like .02346 and returning "2.35%", it depends on your data source, here are two options you can experiment with:
If your underlying data source has a FORMAT() function you can use RAWSQL pass-though to make use of it.
You can then have this inside a case statement based on the Measure field.
See http://www.tableausoftware.com/search/forum/FORMAT%28%29%20function for other examples and related discussion.