6 Replies Latest reply on Nov 22, 2014 5:13 PM by Shawn Wallwork

    How to combine the results of set and measure filters?

    Alexander Mou

      dataset.png

      The (simplified) problem I have here is to automatically get the count of distinct customer IDs.

      The customers need to satisfy either of the conditions:

      1.In red group

      2.Having sum(balance)>=1000: total balance of savings+checking>1000.

       

      The first is a set filter and the second is an aggregated measure filter. I had a hard time combining both.

      Your help and suggestion are highly appreciated.

       

      A workbook is attached, containing a made-up example.

      https://public.tableausoftware.com/views/SetandMeasurefilter/DataSet?:embed=y&:display_count=no

        • 1. Re: How to combine the results of set and measure filters?
          Venkat Madan

          PFA, i have added the logic in Filter calculated field, You can find the count of distinct customers in Sheet 5. please let me know if it is right

          • 2. Re: How to combine the results of set and measure filters?
            Alexander Mou

            Thanks for your response! Your logic is not what is expected:

            IIF([Group]="RED" or ([Group]<>"RED" and [Balance]>=1000),true,false)

             

            It's the sum of both savings and checking accounts that must be >1000:

            sum(balance)>1000, not balance>1000.

            The expected logic is something like

            IIF([Group]="RED" or ([Group]<>"RED" and sum([Balance])>=1000),true,false)

            But this is not grammatically acceptable in Tableau.

             

            In this example, the expected result is 8: 5 reds + 3 greens.

            • 3. Re: How to combine the results of set and measure filters?
              Shawn Wallwork

              Alexander, see attached.

               

              Je ai réécrit votre formule pour le faire fonctionner :

               

              LOOKUP( IIF(ATTR([Group])="RED" or (ATTR([Group])<>"RED" and sum([Balance])>=1000),TRUE,FALSE),0 )


              La première chose que je ai fixé était la question de l'agrégation en utilisant ATTR () pour agréger le champ Groupe. Mais cela ne ira pas sur le plateau de filtre , car il doit être calculé dans Tableau sur les résultats retournés , au lieu de la source de données . Pour cela, nous devons le transformer en un calcul de table faire:


              LOOKUP([Expression], 0)


              Ce est un «neutre» calc tableau qui fait le calc pour chaque ligne de données. Ce est un outil très pratique . Chaque fois que vous utilisez calculs de table vous devez être conscient (et compte ) toutes les dimensions de la vue . Chaque fois que vous ajoutez ou soustrayez une dimension Vous avez le potentiel de « rupture » le calcul .


              Je sais que quelques mots de français , donc je suis en utilisant Google Translate pour écrire cela. Toutes mes excuses si il ya des erreurs .


              [English:

              I rewrote your formula to make it work:

               

              LOOKUP( IIF(ATTR([Group])="RED" or (ATTR([Group])<>"RED" and sum([Balance])>=1000),TRUE,FALSE),0 )

               

              The first thing I fixed was the aggregation issue by using ATTR() to aggregate the Group field. But this won't go on the filter shelf because it needs to be calculated in Tableau on the returned results, instead of in the data source. To do this we need to turn it into a table calculation:

               

              LOOKUP([Expression], 0)

               

              This is a 'neutral' table calc that does the calc for each row of data. It's a very handy tool. Any time you are using table calculations you need to be aware of (and account for) all the dimensions in the view. Each time you add or subtract a dimension you have the potential of 'breaking' the calculation.]



              --Shawn

              • 4. Re: How to combine the results of set and measure filters?
                Alexander Mou

                awesome use of the jedi filter!

                • 5. Re: How to combine the results of set and measure filters?
                  Alexander Mou

                  Got the final count cleaned up in this workbook:

                  https://public.tableausoftware.com/profile/a.m.5517#!/vizhome/SetandMeasurefilter/DataSet

                   

                  Got 2 more questions:

                  1.In the calculated field "Customer Total", I used Last()=0 and it works. Actually last()= any of 0-7 will work.

                  However if using first()=0 or first()=1, the result will no longer work. Why not first()? Thought first() and last() are just different pointers to the same column of data.

                  filter.png

                   

                  2.In the tab "Funny Result", I showed a table using Tableau's column total function. It just looks funny. I know it's the result of applying the jedi filter. But it looks not right. Any remedy?

                   

                  Grand Total.png

                  • 6. Re: How to combine the results of set and measure filters?
                    Shawn Wallwork

                    I'll take a look at this tomorrow, but in the meantime I suggest you get acquainted with my fellow Zen Master Jonathan Drummey and his excellent Drawing with Numbers | Thoughts on data visualization and Tableau

                     

                    Search his blog 'Grand Total' and it should answer the question raised. Also read all his ramblings on Table Calculations. His blog is the resource most of us refer to when attempting to answer the really sticky questions. In fact I suggest that now that you are in the Advanced parts of Tableau, that you search his blog before posting any advanced question.

                     

                    Cheers,

                     

                    --Shawn