1 Reply Latest reply on Oct 8, 2013 12:26 PM by dave.whent

    Need help with table calc compute using to get around padding

    Jonathan Drummey

      This seems like one for Richard Leeke. I'm continuing work on the provider rankings workbook I initially brought up here: http://community.tableau.com/thread/117437 and have run into an issue where I can get a ranking calculation to work when all dimensions are on the Rows shelf, but as soon as I move a dimension onto Columns the calculation returns incorrect results because Tableau v7 is padding, similar to what was talked about in this thread: http://community.tableau.com/thread/117850.

       

      Here’s a description of what I’m trying to do in the attached workbook with sample data, which has more notes in the captions. I’m looking for help in finding the right Compute Using/At the level/etc. settings that will get around the padding that Tableau is doing. I’m using Tableau Desktop Professional 7.0.3.

       

      Thanks,

       

      Jonathan

       

       

      ==Goal

       

      The goal is to be able to display the provider's percentile rank and overall rank based on their rankings across a set of measures.

       

      ==More Description

      Each provider (physician) has a variety of metrics (measures) that are recorded. In MS Access we've taken a subset of the metrics and used the Rate for each to create a Dimensional Rank, and then from those Dimensional Ranks calculated an Average Rank for that provider.

       

      In Tableau, the goal is to use that Average Rank to compute an Overall Provider Rank.

       

      Not every provider has a measure for every time period.

       

      Also, given that we are dealing with already aggregated data, there is only one row in the dataset per Provider/Measure/Measure Date/Rank Type. (There are different levels of Ranking that are done within Access, we're just looking at Rank Type: Office for now).

       

      ==Problem

       

      The results are as predicted and work when calculated with all dimensions on Rows. However, when we move to the desired output where a mix of dimensions on Rows and Columns is used (specifically moving the Provider to Columns), Tableau's padding behavior "breaks" the [Overall Provider Rank (orig)] calculation by returning extra values for the padding where there is no provider/measure data in the underlying dataset.

       

      I haven't been able to find any combination of Compute using, At the level, etc. that returns correct results.

       

      I have been able to find a workaround by taking the incorrect results (using the [Overall Provider Rank (feeder)] calc and then creating the Overall Provider Rank calc that is simply WINDOW_MIN([Overall Provider Rank (feeder)]).

       

      What I'd like to know is if there is some setting for Compute Using/At the level/etc. that will work for the Overall Provider Rank (orig) calculation in the desired view.