3 Replies Latest reply on Apr 18, 2012 3:34 PM by sirajmandayippurath

    Is it possible to create and use a Table Calculation that computes across a field that is not displayed on the grid?

    sirajmandayippurath

      Hi All,

      I have created two Table Calculations in the attached workbook - one to calculate the Average value across each Product Category and the second to calculate a rating which is worked out as the Product Value divided by the Average.

      If I then wanted to remove any of the pills from the Columns shelf and leave it on the filter, the Table Calculation is broken - as expected, I think. If I took a copy of the pills and left it on the Filter shelf, the calculation results vary when I select a subset of data. I think what it is doing is that the average and the rating is then worked out based on what data is made visible to the user.

       

      Does anyone know of a work around to get the Table Calculation to ignore the fact that lesser data is presented on the grid? Can we use Parameters to get this working?

      Thanks in advance!

       

      Cheers
      Siraj

        • 1. Re: Is it possible to create and use a Table Calculation that computes across a field that is not displayed on the grid?
          Jonathan Drummey

          Hello Siraj,

           

          One way I think of table calculations is that all the dimensions*** on the Rows, Columns, and Marks card are used by Tableau to identify the dimensions that create a "canvas" from which the table calculations pluck numbers from based on their settings. ***blue (discrete) pills excepting other discrete table calculations, discrete aggregated measures that are by default ignored in table calculations, and including green (continuous) pills that are dimensions.

           

          Tableau applies context filters and regular filters as it is pulling data from the database, then it performs table calculations. So any normal filters remove data from all calculations, and as you noted having dimensions on the filters shelf does not make them available to table calculations in the view. The method around this is that Tableau can also use the results of table calculations as a filter. In that case, data that is filtered out by the results of those table calculations is simply hidden from the view. One easy way to start hiding certain dimensions while leaving the rest of the calculations untouched is to use a calculated field of the form LOOKUP(ATTR([field]),0) and put that on the Filter shelf. It effectively replaces [field] on the Filter shelf with a table calc that returns the same data. In the attached workbook I included a version of this for both the Region and Country.

           

          Another (sometimes more complicated) way of enabling a subset of data to be shown while totals remain unchanged is to duplicate your data source and set up a blend, with the subset of data as the primary and the relationships set up such that all data is returned from the secondary to be available in calculations.

           

          Finally, I believe your average calculation is not returning a true per-sale average. When the partitioning is set up as you describe, the WINDOW_COUNT(SUM([Sales])) only returns a value of 3 (the set of Germany, UK, USA for each Product), whereas there are actually 6 records in that partition. TOTAL(SUM([Number of Records])) will give you the result of 6.

           

          Jonathan

          • 2. Re: Is it possible to create and use a Table Calculation that computes across a field that is not displayed on the grid?
            Jonathan Drummey

            One more thing, relating to the subject of your post. You can definitely have fields that are part of the calculation that are not displayed, that's a main purpose of the Level of Detail shelf on the Marks card (the other is to add fields to the view to be displayed in tooltips and reference lines).