6 Replies Latest reply on May 4, 2012 12:36 PM by Alex Kerin

    Filtering data in a calculation

    Robert Sinclair

      I've seen several posts about using filters in a calculation, but I'm still unclear how to solve my problem.  I have attached a packaged workbook to illustrate the issue.  My data contains three columns:

      Data.pngtabshot.png

      There are two scores associated with every store, designated either as model 1 or 2. I with to plot the intersection of these two scores on a scatter plot.  There can be several Model 1 and Model 2 scores associated with each store, therefore I wish to plot the average of each model on my chart. 

       

      Calc A is:  "if ATTR([Model])=1 then avg([Score]) else Null end"

      Calc B is:  "if ATTR([Model])=2 then avg([Score]) else Null end"

       

      However, the results I get are 333 nulls.  I know I could fix this if I reformatted my data by moving Model 2 scores to another column, but I don't wish to have to reformat data until I have exhausted that there is NO WAY to do this in Tableau.  Your help is much appreciated!

       

      Thanks

      Robert

        • 1. Re: Filtering data in a calculation
          Alex Kerin

          It's possible I've missed something, but changing your calcs to:

           

          if ([Model])=1 then ([Score]) else Null end

          and

          if ([Model])=2 then ([Score]) else Null end

           

          And dragging these up, changing the aggregate to avg instead of sum, and having store on the level of detail seems to work.

           

          5-3-2012 3-06-09 PM.png

          • 2. Re: Filtering data in a calculation
            Robert Sinclair

            Thanks Alex and Derek,

             

            You both exposed the error of my ways.  Tableau does does not allow the mix of aggregate data and non-aggregate data within an "IF" statement.  That's been a hard one for me to allow to sink in. In my roadblock moment I found myself tinkering with my fix (and wondering why it wasn't working), which was wrapping my non-aggregated data with the "ATTR()" function.

            error.png

            [Score] is a calculated (and aggregated) field. And although Tableau excepts this calculation, it does not provide any data, just Null for each corresponding data point.  Why this fundamentally does not work, I'm still unclear on. However as Alex pointed out, the fix was to write the calc like this:

            fix.png

            and the aggregate the pill on the shelf with AVG().

             

            I hope this helps other avoid the frustration I experienced.

            • 3. Re: Filtering data in a calculation
              Alex Kerin

              To round this out - the first equation you had was an aggregate - akin to a formula you may have at the bottom of a column in Excel.

               

              The input to attr can only be one value across the whole of the column (or partition with table calculations) - that is the purpose of attr. You were feeding 1 and 2 to attr which results in * which then returns null from your equation.

              • 4. Re: Filtering data in a calculation
                Frank Forestell

                Hey, Robert. I find that I get trapped in the aggregate in an IF scenario as well. Do you think Tableau will be able to change this or is it really impossible to do?

                 

                Regards,

                 

                Frank

                • 5. Re: Filtering data in a calculation
                  Robert Sinclair

                  Frank,

                   

                  Good question. However, I don't know that I'm qualified to even offer up an educated guess.  I don't know where Tableau programmers are bound by the laws governing SQL and where they are not.  If mixing aggregate and non-aggregate data in a "IF" statement is restricted by SQL, then it may not be something they can easily address.

                   

                  We'll have to wait and see.  It's fun to think about the direction Tableau may head over the next five years.

                   

                  Best,

                  Robert

                  • 6. Re: Filtering data in a calculation
                    Alex Kerin

                    There isn't really any handling that needs doing - because the calls to the db (even to Excel) can produce different rows and columns, the concept of a flat file isn't there.

                     

                    In Excel we may have a calculation on every row that also calls out to a referenced absolute cell (e.g. this row value/the sum at the bottom which is absolute)

                     

                    This works because that flat file isn't changing - the row isn't suddenly going to change to a different level of aggregation. This is not the case for Tableau where calculations are more flexible.

                     

                    Therefore we have to specify how to handle this - do we want to sum all the values now before we divide, or do we know that there will only ever be one value fed over (then we can use the attr aggregation)?

                     

                    It takes a while to understand this, but this is the way it should work.