10 Replies Latest reply on Feb 16, 2017 1:47 PM by Kay Ahn

    How to write 'select all' in calculated field

    Kay Ahn

      Hi, I set up a calculated field to bring up Goal % for each leader, and there is a filter where users can choose which leader's goal they want to see.

      However, when users select 'All' in the current filter, I want to show a certain number (0.9 in below example), not the average of these 4 numbers.

      Is there any way to do this without creating a separate workbook?

      any insights will be much appreciated!

      thanks in advance!!

        • 1. Re: How to write 'select all' in calculated field
          Norbert Maijoor

          Hi Kay,


          Not sure but find my approach based on parameter as reference below and stored in attached workbook version 10.1


          1 of 1 people found this helpful
          • 2. Re: How to write 'select all' in calculated field
            Deepak Rai

            Hi Norbert,

            Great!!. I liked the way you turned it around. Learned a new trick today from you!!!



            • 4. Re: How to write 'select all' in calculated field
              Kay Ahn

              Thanks Norbert, but my actual datafile already has a parameter (whether you want to see group names or leader names) and then filters (from group head to their direct reports), so I was hoping to do it as a calculated field rather than adding another filter/parameter..

              • 5. Re: How to write 'select all' in calculated field
                Joe Oppelt

                See attached.  I changed the [Goal] calc

                • 6. Re: How to write 'select all' in calculated field
                  Kay Ahn

                  Thanks Joe. Just one more question, what about when there is additional condition to the if statement,


                  IF attr([Group Leader 2])="GH 1" AND ATTR([Job Level])="CEO" then 0.1

                  ELSEIF attr([Group Leader 2])="GH 1" AND ATTR([Job Level])="COO" then 0.2

                  IF attr([Group Leader 2])="GH 2" AND ATTR([Job Level])="CEO" then 0.4

                  ELSEIF attr([Group Leader 2])="GH 2" AND ATTR([Job Level])="COO" then 0.5


                  and I want to show each of the overall goal for CEO and COO in aggregate, is this possible?


                  thanks for your help!


                  • 7. Re: How to write 'select all' in calculated field
                    Joe Oppelt

                    In the attached I simulated what you did, but for L07 and L08 from [Job Level Group].  I narrowed it down to those two.


                    I have to have that dimension on the sheet to separate out the values, otherwise the logic goes into the "all other" bucket because for any "GH 1" there are multiple values and tableau just says, "huh?". 


                    See attached.


                    THe logic in [Goal] can get as complicated as you need it to be.  Maybe the value for one level is always one bump up from the lower level, for instance.  For now I just used the sledgehammer and spelled out each condition, but maybe the algorithm can be more elegant.

                    • 8. Re: How to write 'select all' in calculated field
                      Kay Ahn

                      But I have, in this example, separate goals for overall L07 and L08. So I want to add at the end,

                      if (all group leader) is selected, AND job level is L07 then 0.9

                      elseif (all group leader) AND job level is L08, then 0.99

                      something like that.


                      I tried at the end, and at the beginning,

                      elseif job level=L07 then 0.9

                      elseif job level=L08 then 0.99


                      (so didn't include group leader condition) but it doesnt work can you help?

                      • 9. Re: How to write 'select all' in calculated field
                        Joe Oppelt

                        Yes!  I can help! 


                        Check out the attached.

                        There are actually multiple ways to approach this.


                        First of all, I changed [Goal] to do what you are looking for -- in the context of the data we have on hand.  (Ignore the 4 beneath the value for now.)


                        But I notice if we select all Job Levels (give it a try) we get displays for all the levels, and maybe you don't want that.  (Maybe you do, and we're done here!)


                        You can do some things to know what "All" really represents, and whether or not your user has selected "all".  (For the record, "All" isn't an actual value in Tableau.  It's an added in function of convenience for your users so that they don't have to select 100 boxes if there are actually 100 choices in the filter list.  See Sheet 1a.  In there I customized the Job level filter to remove the "All" value.  That's what the filter choices really are as defined by your data.)


                        Anyway, in sheet 1 I added a calc -- [Count Group Leader 2 in ALL].  This is using LOD to see how many actual Leader2 values are in your data source.  A FIXED calc evaluates before any other filters activate, so you get your count of data as it comes into Tableau.  And then I created a COUNTD LOD calc to count what is actually selected on the sheet.  This tells tableau to do a countD, but ignore the partitioning across [Job Level].  Essentially do a COUNTD across the whole sheet.  I put these in the title so you can see what's happening with those numbers.


                        On Sheet 2 I created a new filter to replace the quick filter on Job Level Group.  This is a table calc.  And when you use a table calc, the filter doesn't eliminate rows from your table.  It just limits what part of the table gets displayed.  It still works like a filter, and you can even display it like a quick filter.  This may or may not be important to your situation, but your actual application and data may need this.


                        Sheet 2 is still using the [Goal] calc that I modified.

                        Now look at Sheet 3.


                        I made a copy of [Goal].  In this one I used the two new COUNTD calcs to determine whether or not ALL was selected for Group Leader.  This time I put the check for that portion of the logic at the top.  Just because... 


                        Anyway, I did this to show you ways to handle what you need to do.  The COUNTD method will be useful to you in lots of places.  You can know what the user has selected, and act accordingly.

                        • 10. Re: How to write 'select all' in calculated field
                          Kay Ahn

                          Thank you so much Joe, you are a huge help!

                          Although, my '[Count Group Leader 2 in ALL]' wasn't working, it kept changing as the group leader filter changes, so I just did

                          IF [Count Group Leader selected] = actual number of leaders, since it doesn't change. It's not ideal, but this will do.

                          Thanks again Joe!