3 Replies Latest reply on Jan 18, 2014 11:54 PM by Jim Wahl

    Simple Solution Using Table Calculation (I hope!)

    Chris Moseley

      I am having trouble getting data to display in a Table.  I have attached a sample workbook using fake data to illustrate my problem.  I suspect it is just using the correct table calculation but I've tried a number of approaches and was unable to come up with the solution.


      As shown in the attached, I am trying to build a table similar to the one in tab "Just New Orders."  This table displays just products that have shipped in last day ("New Orders") arranged by Employer (i.e. parent company), Location (i.e. branch office) and product. 

       

      The first two columns of data are displaying as intended: they show (1) the count of new orders and (2) The total number of orders out in the field. 

       

      The problem is with the third column of data; the historical average days from order to arrival for each product.  This currently displays the historical average for the product at that specific location.  What I want is for it to show the average for ALL deliveries of that particular product, not just at this location. 

       

      This figure is derived correctly on the tab "Average Days by Product."   I want all Mug line items  to show the same historical market average of 48 days.  All Paper should show market avg of 39 Days, Rocks = 43 days, etc. 

       

      I believe two factors are complicating me getting the desired result.  1) Having the Table broken down first by employer and location requires a table calculation.  I tried a number of iterations, primarily with Window_Avg() but never got exact right results.  2) the filter limiting the list to displaying only New Orders also limits the data set which is being evaluated by my current calculation.  I want this to take into account the whole history, like on tab "Average Days by Product."

       

      Any help much appreciated.

        • 1. Re: Simple Solution Using Table Calculation (I hope!)
          Jim Wahl

          Hi Chris,

           

          There are a couple of way to fix this. I'll describe both, but the easiest is to skip the table calcs and use a duplicate data source.

           

          Duplicate Data Source

          Just click the current data source > Duplicate. From the secondary data source, drag Historical Days to Arrive to the Measures Values shelf and select AVG.

           

          Since you don't want to calculate this value based on Employer or Location, click the secondary data source and click the orange chain-links next to these dimensions. Or course now if you filter an Employer or Location in the primary data source, your AVG() will not be updated accordingly, ...

           

          See the attached tab "Fixed Using Dup Data Source".

           

           

          Using Table Calcs

          Nothing could be easier than the above, but you found a couple of interesting issues with table calcs that might be helpful to understand down the road.

           

          1. WINDOW_AVG() vs TOTAL()

          Window_XXX functions are an aggregate of an aggregate. WINDOW_AVG(AVG(Days to Arrive)) averages all of the average days to arrive across the partition.

           

          TOTAL() is an aggregate at a higher level of detail. TOTAL(AVG(Days to Arrive)) will average all of the [Days to Arrive] values across the partition.

           

          In this case you want TOTAL(AVG(...))

           

          I created a field Calculation2 (copy) that replaces WINDOW_AVG with TOTAL. When you add this to the view, click the pill and select Edit Table Calculation > Compute Using > Advanced and move all of the dimensions, except Product, to the right-hand Addressing side of the dialog box.

           

           

          2. Filtered Data

          It still doesn't work, because you're filtering values where the count of New Orders is 0. For example Employer D -> Location - D1 has no new orders for mugs and, therefore, the historical orders are not in the view and are not included in the Calculation2 (copy) average. You can check this by removing the filter.

           

          You can fix this this with a "table calc filter." Tableau applies most filters when it queries the data source. The big exception is table calculations. Table calculations are done within Tableau and, therefore, filters based on table calculation are done within the view after the other calculations, including other table calculations.

           

          Creating a table calc filter is easy, if not intuitive.

          New Orders Table Filter = LOOKUP(COUNT([New Orders]), 0)


          LOOKUP() doesn't do anything except turn COUNT(New Orders) into a table calculation. But now you can put this new filed on the filter shelf and set the filter as you did previously.


          2b. BUT this breaks (or fixes?) Total Open Orders Calculation

          The calculations for New Orders and Open Orders are also done before the table calc filter.


          This means that your Open Orders calculation now includes all products with open orders, even if they have no new orders. Check the Total rows for Total Open Orders, and you'll see the sum != the visible values---again the table calc filter is hiding, not excluding, the data.


          I'm not sure if this is what you want or not. It doesn't really make sense to have a total row that doesn't equal the sum of the above values. On the other hand, location A2 has more open orders than shown in the table.


          Fixing this requires some more work, maybe a using custom total calculations with a conditional. In any case I suspect the duplicate data source approach is easier.

          • 2. Re: Simple Solution Using Table Calculation (I hope!)
            Chris Moseley

            Jim,

             

            Thanks so much for the response.  I was kind of circling around these approaches but could not get things to work the way I wanted.  Your detailed reply was just what I needed.   Thanks again!

             

            - ctm

            • 3. Re: Simple Solution Using Table Calculation (I hope!)
              Jim Wahl

              No problem. Thanks for the follow-up.