12 Replies Latest reply on Sep 7, 2011 4:48 AM by Joe Mako

    Can Tableau achieve this report format?

    Alexander Baker

      Have been asked to investigate how I can try and get Tableau to show varying numbers of columns per group of columns. I have two grouping levels and a list of names in each sub group. In the image attached, I want to show columns 'Amount', 'Delta' and 'Gamma' for group 'blah' and only 'Amount' for 'skiberdee'? I am struggling to get Tableau to think about any sort of differing column layout. Does anyone have any ideas on how this can be achieved?

        • 1. Re: Can Tableau achieve this report format?
          Joe Mako

          You will want to reshape your data to enable Tableau to create the layout you want. See http://www.tableausoftware.com/support/knowledge-base/preparing-excel-files-analysis for details.

          • 2. Re: Can Tableau achieve this report format?
            Alexander Baker

            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.

            • 3. Re: Can Tableau achieve this report format?
              Joe Mako

              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. :)

              • 4. Re: Can Tableau achieve this report format?
                Alexander Baker

                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.

                • 5. Re: Can Tableau achieve this report format?
                  Alexander Baker

                  I guess I was hoping for something more elegant :-)

                  • 6. Re: Can Tableau achieve this report format?
                    Joe Mako

                    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?

                    • 7. Re: Can Tableau achieve this report format?
                      Alexander Baker

                      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

                      • 8. Re: Can Tableau achieve this report format?
                        Joe Mako

                        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.

                        • 9. Re: Can Tableau achieve this report format?
                          Alexander Baker

                          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.

                          • 10. Re: Can Tableau achieve this report format?
                            Joe Mako

                            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.

                            • 11. Re: Can Tableau achieve this report format?
                              Alexander Baker

                              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?

                              • 12. Re: Can Tableau achieve this report format?
                                Joe Mako

                                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:

                                 

                                 

                                STR(INT(ROUND([VALUE]*100,2)))+"."+
                                
                                 RIGHT(STR(INT(ROUND([VALUE]*10000))+10000),2)+"%"
                                


                                 

                                or

                                 

                                 

                                STR(ROUND([VALUE]*100,2))+"%"


                                 

                                or

                                 

                                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.