10 Replies Latest reply on May 2, 2017 11:25 PM by michael betterton

    Using a Table Calculation as Filter

    michael betterton

      I have a data set that requires the use of a table calculation in order to properly filter the data set to a single month.

       

      The data set is at the monthly grain and I use the calculated Field "Reporting Month Filter = LOOKUP(MIN(([Reporting Date])),0) " to filter the data set. This meant YTD calculations functioned correctly without filtering the underlying data set. This process is clearly defined in the following articles:

           Filtering the View Without Filtering Underlying Data | Tableau Software

           A Jedi (filter and table calc) trick | Tableau Software

       

      In Tableau 9 this produced the following result. Note the grand total aggregates and performs the calculation correctly.

      V9.png

       

      After upgrading to Tableau 10.1.1, the report, when opened in Desktop and Server, no longer aggregates the grand total and instead produces the following:

      V10.PNG

       

      No changes have been made to the workbook, there are no extra level of detail expressions in the worksheet. I have attempted to multiple versions of show/hide functionality but none allow for the grand total to aggregate correctly.

       

      For data sensitivity purposes I cannot post the workbook, although I have created an example workbook that replicates the issue. Any help would be appreciated on workarounds for the issue.

       

      Thanks

        • 1. Re: Using a Table Calculation as Filter
          Sireesha J

          Hi Micheal!

           

          The Grand Total is in your example is showing values for all the dates/months. This is because the detail level is at month level i.e., in the marks card you have dragged date fields at month and year level and not at exact date level. At the same time you are filtering the data at a particular day/month level. So, it is returning multiple values. Whenever you filter the data make sure same field is used table calculations in such cases.

           

          Hope, it helps you.

           

          Regards,

          Sireesha.

          • 2. Re: Using a Table Calculation as Filter
            michael betterton

            Hi Sireesha,

             

            I'm sorry but your solution is incorrect. The reason the two different date filters are required is because your solution has filtered the underlying data source.

             

            As you correctly identified the grand total is showing every month. When you hover over December 2016 you'll see the correct grand total is 18.67. In your example, the grand total has aggregated the previous columns (summing the result) causing you to attain the incorrect answer of 23.0. This is due to the difference of performing a division based on the population vs performing the division with a subset then aggregating.

             

            In Tableau 9.1, when either of the two tutorials I linked were followed, the calculation was performed differently for the grand total and the correct value as achieved. Since upgrading to Tableau 10.1, the grand total now behaves differently.

             

            Thanks for your efforts though but the issue still remains.

            • 3. Re: Using a Table Calculation as Filter
              Bora Beran

              Hi Michael,

              There is currently no good workaround to get the table calc to filter out those extra rows in totals. We're working on a fix for it.

               

              Thank you,

               

              Bora

              • 4. Re: Using a Table Calculation as Filter
                michael betterton

                Hi Bora,

                 

                Thanks for that, good to know it isn't just me!

                 

                What is the best way to find out when this fix is added?

                 

                Michael

                • 5. Re: Using a Table Calculation as Filter
                  Bora Beran

                  Hi Michael,

                  Our release notes include the list of issues fixed in each release.

                   

                  Here is an example for 10.0.4 release

                  10.0.4 | Tableau Software

                   

                  I can also respond to this thread again once the fix is complete.

                   

                  Thanks,

                   

                  Bora

                  • 6. Re: Using a Table Calculation as Filter
                    Jonathan Drummey

                    Hi Michael,

                     

                    I've got a different take on this than Bora, plus three possible workarounds. In Tableau up until some point in v9 table calc filters did *not* affect Grand Totals in any way, then for some set of releases during v9 they did, and then that "defect" was "corrected" somewhere in the v10 release cycle so that explains the behavior that you're seeing.

                     

                    I believe the question of table calc filters affecting grand totals (GT) is a complicated one because there are two (at least) very different use cases:

                     

                    1)  The case here where the GT has an increased level of detail (due to the Month dimension on the LOD shelf) and the goal is to filter that dimension for a single value in both the GT and detail rows while preserving results for a table calculation measure.

                     

                    2) The more common situation I've seen where there's a measure that is a table calculation and by adding a TC filter based on the measure the user wants to filter the detail rows and then have the GT reflect only the remaining values. If the TC filter affected both the GT and detail rows then in this case it would a) show the full range of values including the detail rows and GT and b) would likely be filtering out the GT values that users want to see. I first found and reported the change in behavior in v9 when working with clients whose use case #2 views had broken after upgrading to v9. Tableau generally sees breaking views from one release to another as a defect, so that's why the defect was "corrected".

                     

                    Personally, my favorite "Tableau builds something" solution would be for Tableau to implement running calculations in LOD expressions, then the LOD expression could do the running total based on the finer grain of month and we wouldn't have to deal with the added complexity of filtering the GT. Making table calc filters affect the GT would need to deal with those two use cases and I see that as adding complexity to Tableau and GTs and table calcs are already too complicated, IMO.

                     

                    There are some workarounds for this, none of them are the "perfect" solution but I've used all three in different circumstances. I prepared versions of each in the attached workbook.

                     

                    1) Use a parameter instead of a table calculation filter. Since parameters can be used at any level of calculation can avoid the fundamental problem of not being able to filter the extra marks in GT by not having any extra marks in the GT. There are record-level feeder calculations that identify Months & Paid FTE rows for inclusion in the ASL calc and then without the Month dimension in the view only a single result is returned in the GT. Another way of looking at this is that instead of having a table calc and table calc filter, we swap things around and are putting the filter inside record-level calcs that are aggregated to the minimal viz level of detail (vizLOD) that is necessary.

                     

                    The problem here, of course, is that the list of parameter values is not dynamic and would need to be manually updated. (@Bora, this is one argument for having a dynamically refreshing list of parameter values, for dealing with the variety of use cases where Tableau has yet to give us the kinds of controls that would ideally exist).

                     

                    Here's a pic of the parameter workout w/dates view in the attached workbook:

                     

                    Screen Shot 2016-12-23 at 10.09.16 AM.png

                     

                    And the final parameter view:

                    Screen Shot 2016-12-23 at 10.09.46 AM.png

                     

                     

                    2) Awhile back I came up with a "dynamic" parameter workaround that uses a Tableau data blend to return a single value (such as a chosen month) from a secondary source, see Creating a Dynamic “Parameter” with a Tableau Data Blend | Drawing with Numbers for more details on that. I implemented that here, this is the final blend view:

                     

                    Screen Shot 2016-12-23 at 10.11.55 AM.png

                     

                    The challenge here is that because we can only work with secondary calcs via aggregations then we must have Month in the vizLOD, that means that the calculations are more complicated and we need to turn off Analysis->Stack Marks to get the table layout to look right. In addition Tableau data blends always show Null as a value in the filter list.

                     

                    3) The third solution pads out the data so the running sum can be computed entirely as a regular aggregate within each month. From a conceptual standpoint we can think of a running sum as aggregating each mark N times, once for each address in the partition. So instead of using table calculations to do that aggregation we can pad out the data via a cross product (xprod for short)so that each record exists N times, once for each address, and then all we need to do is a regular aggregation to the vizLOD and filter out extraneous months.

                     

                    So I built this by creating a new data source that does a self-join on Team so every Month exists for every other Month, and added a Data Source filter that ensures that each original Month only has extra records for the prior months to the current Month. Then the ASL calc becomes SUM([Paid FTE (Sheet11)])/COUNTD([Month (Sheet11)]) where Sheet11 is identifying the padded-in months, here's the final xprod view:

                     

                    Screen Shot 2016-12-23 at 10.23.09 AM.png

                     

                    What I see as the two problems here are the added complexity and that the cross-product is creating extra rows (which may not be usable given large data volumes or require even more data prep to pre-aggregate the data). However, this workaround avoids the problem of the parameter in workaround #1 by having completely dynamic list of values (since it's based in the data) and doesn't have any of the Null values or data blending complexity of workaround #2, so it's generally my preferred solution if I have choice.

                     

                    Jonathan

                    2 of 2 people found this helpful
                    • 7. Re: Using a Table Calculation as Filter
                      michael betterton

                      Hi Bora,

                       

                      I've been watching the release notes but I don't think this issue has been fixed yet.

                       

                      Is this correct?

                       

                      Thanks,

                      Michael.

                      • 8. Re: Using a Table Calculation as Filter
                        Bora Beran

                        Hi Michael,

                        That is correct. It has not yet shipped but it is work in progress.

                         

                        Thank you,

                         

                        Bora

                        • 9. Re: Using a Table Calculation as Filter
                          Bora Beran

                          The change is in 10.3 Beta. You should see it as a new option "Apply to Totals" in the context menu of pill on Filters shelf.

                          • 10. Re: Using a Table Calculation as Filter
                            michael betterton

                            Cheers for working on this Bora Beran