2 Replies Latest reply on Mar 4, 2014 9:35 AM by Philip Groeting

    Complex selection: select a group in which exists...

    Philip Groeting

      Hello,

       

      I am stumbling over a selection puzzle in one of my Tableau workbooks which I couldn't even express in SQL, but maybe there ist someone having an idea...

      I have a dataset looking like this

      2014-03-03_09h52_14.png

      It contains some lines, where for a {costcentre, costtype} lines exist, in which the actual exceeds the budget. The costtypes are grouped.

      The desired resultset would be to filter to those groups, where a cost overrun exists, but then take all the lines from that group:

      2014-03-03_09h57_09.png

       

      My question is: is something like that even possible?

      In Tableau, whether this is done by doing some magic on the datasource or by some calculated field, I wouldn't know. At the moment, I would do 2 steps at data loading time, which is less dynamic (first setting a field "above_budget" on the red lines, then extending this marker to all green lines).

        • 1. Re: Complex selection: select a group in which exists...
          Dylan Snyder

          Hi Philip,

           

          Give this a try:

           

          1) Create a calculated field that checks whether actual > budget. Something like:

          WINDOW_MAX(IF ATTR([ACTUAL]) > ATTR([BUDGET]) THEN 1 ELSE 0 END)

          2) Add this calculated field to your crosstab and edit your table calculation (right click measure > edit table calculation) so that you partition by contcentre and costtype_group. This will make all rows for each costcentre/costtype_group 1 if any of the rows within their partition meet the criteria of your calculated field.

           

          3) Add this field as a filter and remove all rows (costcentre/costtype_group combinations) where your calculated field = 0.

          • 2. Re: Complex selection: select a group in which exists...
            Philip Groeting

            Thanks a lot, Dylan!

            I would never have come up with such a way and it took me a while to understand. I've got it now, it's working perfectly fine and it's incredibly easy!

             

            Cheers,

            Philip