1 2 Previous Next 27 Replies Latest reply on May 21, 2017 4:08 PM by parvinder.bindra.0 Go to original post Branched to a new discussion.
      • 15. Re: Table Calculation Callenge
        Shinichiro Murakami

        Change the filter to context.

         

        Shin

        1 of 1 people found this helpful
        • 16. Re: Table Calculation Callenge
          parvinder.bindra.0

          Shin,

          One more thing how to change the width of the column as in actual data sheet I have 6 columns and its getting really wide and unmanageable.

          Thanks,

          Parvinder

          • 17. Re: Table Calculation Callenge
            Shinichiro Murakami

            No way, unfortunately.

             

            Thanks,

            Shin

            • 18. Re: Table Calculation Callenge
              Shinichiro Murakami

              Actually, what I had been explained is NOT standard approach at all.

              Adding numbers under dimension description is really challenging request which I can say "Not Impossible".

               

              You can give up that option and merge on dashboard which is more flexible with the capability of changing width.

               

              That does not require any complicated format and much much more reasonable for most of the situation.

               

               

              Thanks,

              Shin

              • 19. Re: Table Calculation Callenge
                parvinder.bindra.0

                Hi Shin, what all should I do separately and merge on dashboard.

                Please help. I don't think there is any other way to get dimension grand totals any other way.

                Thanks,

                Parvinder

                • 20. Re: Table Calculation Callenge
                  parvinder.bindra.0

                  Jonathan Drummey,

                   

                  Please help, The approach mentioned in this question gives me everything but in the end, my sheet gets very wide as I have other fields to add as well, so do we any other approach where-in we have flexibility to change column width.

                   

                  Thanks,

                  Parvinder

                  • 21. Re: Table Calculation Callenge
                    Shinichiro Murakami

                     

                    Create two more worksheet just show calculate result of count of region and segment respectively.

                    Hide all the header and lines, boarder.

                    If you need filter, change them to context and share across three worksheets.

                     

                    Put these sheets as "Floating" on dashboard.

                     

                    That's pretty straight forward way which you can learn on official sites' training video or something

                    about "Creating dashboard".

                     

                    Thanks,

                    Shin

                    • 22. Re: Table Calculation Callenge
                      Shinichiro Murakami

                      Or little bit modified way to combine the sheets on dashboard.

                       

                       

                      Create header part and measure part separately.

                      (actually copy existing sheets and delete left half and right half respectively.)

                       

                      Then merge on dashboard and change the width appropriately with using blank frames..

                      Then fit width for both header and measure worksheets.

                       

                      Thanks,

                      Shin.

                      • 23. Re: Table Calculation Callenge
                        parvinder.bindra.0

                        You can't sync those sheets if they are separate that's the issue with that approach.

                        • 24. Re: Table Calculation Callenge
                          Jonathan Drummey

                          Hi,

                           

                          Whoever is giving you requirements is designing visualizations from the mindset of traditional tabular reporting and/or Microsoft Excel. I start out by writing this because Tableau doesn't work the same as those tools and the challenges you are running into and the amount of time & number of posts it takes for volunteers like Shin and others to help directly stem from that lack of understanding of how Tableau approaches visualizing data. This lack of understanding on the part of the designers/requirements gatherers needs to be resolved or else a) you'll continue to be challenged in delivering visualizations and b) even worse, users will be unsatisfied because they are continually trying to stuff square pegs (visualizations designed for MS Excel) into a round hole (Tableau).

                           

                          While it is technically possible in Tableau to create visualizations that look like the requirements, if the requirements change at all or are not fully specified (such as the case here where the sample data covers 2 measures + 2 dimensions = 4 columns but the actual workbook apparently has 6 columns as per this post Re: Table Calculation Callenge) then the provided solution will likely become unworkable, as is the case here.

                           

                          So while I can provide a solution that can more easily scale out to additional columns a) it's got various drawbacks because we're trying to stuff a visualization into Tableau and b) if and/or when the requirements change then it's more likely this particular view will no longer suffice and require additional work or even an entirely different approach that would require a lot more effort.

                           

                          Here's what I came up with:

                           

                          Screen Shot 2017-05-19 at 10.51.04 AM.png

                           

                          Meeting the requirement plus first possible drawback: With this pill layout the Region and Segment headers can be made as narrow as necessary. The 2 measures (4 in the final view) still have fixed column widths because that is how Tableau works, though as in the view above we can make them quite narrow. Hopefully that meets the requirement. If this doesn't work then I can conceive of another view that would enable individual column widths and independent axis ranges but it would be quite complicated to build and computationally expensive plus difficult to maintain, so I'm not going there.

                           

                          The second drawback (and the key enabler of this viz) is that the Tableau data source has been restructured to have a copy of the original data for each desired column plus an additional row for each column that has values of 'GT' (short for Grand Total) in every field. I think of this kind of source as a "scaffold" source because it has all the dimensionality that we need and from there we can 'paint' the values where they need to go in the viz. This kind of source can be built out manually, with SQL, in Tableau 9.3 and up using union, in Tableau 10 and up using cross data source joins to mimic a union, etc.

                           

                          Here's the workout view before I rearranged pills:

                           

                          Screen Shot 2017-05-19 at 11.05.23 AM.png

                           

                          Third drawback: Because we are "scaffolding" the data there are many values that are used to get the layout that are aliased, plus we end up with extra values in quick filters that must be present for the view to work. Each copy is identified by the Column ID column, which has been aliased to Category and Mfr. The GT values for Category and Segment have been aliased to ZZ-Grand Total so they will appear at the bottom of filter lists.

                           

                          Fourth drawback: The calculations are complicated due to the indirection required by the scaffolding.

                           

                          The Measures calculation has a compute using on Region & Segment and the formula:

                           

                          CASE MIN([Column ID])

                              WHEN 1 THEN //Category

                                  IF MIN([Region]) != 'GT' THEN

                                      COUNTD([Category])

                                  ELSE

                                      WINDOW_SUM(COUNTD([Category]))-1

                                  END

                              WHEN 2 THEN //Segment

                                  IF MIN([Region]) != 'GT' THEN

                                      COUNTD([Manufacturer])

                                  ELSE

                                      WINDOW_SUM(COUNTD([Manufacturer]))-1

                                  END

                          END

                           

                          The -1's are to subtract the GT value that would be included in the Count Distinct. Additional WHEN clauses would be added for additional columns.

                           

                          The Region Header has a compute using on Region & Segment and the formula:

                           

                          IF MIN([Region]) != 'GT' THEN

                              MIN([Region])

                          ELSE

                              STR(TOTAL(COUNTD([Region]))-1)

                          END

                           

                          The Segment Header calculation has the same compute using and essentially the same formula.

                           

                          Now on to the views and the next drawback. In this view i've just hidden the Region and Segment dimensions, what we end up with is replicated values for the Region Header:

                           

                          Screen Shot 2017-05-19 at 11.12.04 AM.png

                           

                          We can work around that by moving Region and Segment to detail (if your calculations break then you'll need to make sure they have a compute using on both Region & Segment), however then we have totals on top which does not meet the requirement for totals on the bottom. Plus the totals are not formatted like other values. Theoretically you might be able to change that by adding another pill to rows and using Tableau's banding and formatting. Alternatively, if totals on top were acceptable you could theoretically create two worksheets, one for just the total on top and the second for the detail rows and then put the two on a dashboard.

                          Screen Shot 2017-05-19 at 11.22.33 AM.png

                           

                          To get totals on the bottom did a manual sort on the Region Header field and it appears to work as values are filtered in and out of the view, however I can't promise that this would always work.

                           

                          Screen Shot 2017-05-19 at 10.51.04 AM.png

                           

                          If you wanted the Regions and Segments to be sorted by some other values then the Region Header & Segment header would need to be computed as FIXED Level of Detail (LOD) expressions instead of a table calculation, and the filters would have to be added to Context (which has performance and maintenance implications).

                           

                          I explored a couple of other options in building this view. One was doing a smaller scaffold of just Region & Header & using a data blend, however that is limited because we need to use a count distinct on the raw data and that has limitations with filtering. Another option would be to have 2N copies where there are N copies for the columns + N copies for the grand total and build out views using a combination of this technique and the technique described in http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-3/, however that is likely too many copies for volume. The one advantage there is that it would get rid of the extra values in the filters for the Grand Totals.

                           

                          Going back to my first couple of paragraphs, building out these views and writing up the descriptions took me over an hour. If I just look at building out the data source and building the views and validating calculations that was at least 30 minutes of that. Here's an alternative view that took me 2 minutes to build using the original data.

                           

                          Screen Shot 2017-05-19 at 11.38.25 AM.png

                           

                          It's got all the information as the original view, the only difference is that I worked *with* Tableau (rather than against it) by putting the summary information of Region and Segment counts into the Title. This view was built 15 times *faster* than the other views because has no extra data preparation, no complicated calculations, no extra values in quick filters, doesn't require context filters, etc. The most typing I had to do was building out the title.

                           

                          Taking the time to educate people on the capabilities (and limitations) of Tableau is always worth the effort because it will save so much time in development, testing, and maintenance, plus increase user satisfaction of the tool.

                           

                          Jonathan

                          3 of 3 people found this helpful
                          • 25. Re: Table Calculation Callenge
                            Rody Zakovich

                            Jonathan Drummey this is a fantastic post, and one that is all too true. "Most" of the struggles people face stem from trying to force Tableau to do something it isn't designed to do (or isn't currently designed to do). I've found, rather than trying to force Tableau to do things via, binning/union densification, nested table calcs, grand total hacks, etc, it is better to rethink the design process from the beginning, the audience/requirements gathering. If we can understand Tableau's capabilities and its' strengths/weaknesses, we can set better expectations for the end users. This, in turn, allows us to create datasources that most effectively promote Tableau's strengths, allowing us to simply "Drag and Drop" pills, as opposed to Tableau wizardry. Thank you for writing this up!

                             

                            -Rody

                            1 of 1 people found this helpful
                            • 26. Re: Table Calculation Callenge
                              parvinder.bindra.0

                              Thank you Jonathan for your extensive reply, kindly share the workbook for the work you did, that will help in understanding the details.

                              Thank you again.

                              Thanks,

                              Parvinder

                              • 27. Re: Table Calculation Callenge
                                parvinder.bindra.0

                                Jonathan,

                                Sorry its there, somehow it was not visible to me earlier, though could get it now.

                                 

                                Pls ignore that.

                                Thank you,

                                Parvinder

                                1 2 Previous Next