10 Replies Latest reply on Jan 6, 2014 11:38 AM by Jonathan Drummey

    How to filter within a group?

    Mounhim Tahtahi

      I have the following situation:

       

      Each record has the following fields:

      CUST_ID TRX_ID AMOUNT DEBITCHANNEL

       

      DEBITCHANNEL is a choice between 5 values: VAL1, VAL2, VAL3, VAL4, VAL5.

       

      I can filter for instance on all records with DEBITCHANNEL = VAL1. So far no problem. But I can't get the following filter working:

       

      Display list of customers (CUST_ID) who only have transactions (TRX_ID) where DEBITCHANNEL is VAL4.

       

      This means customers who have transactions with VAL1 and e.g. VAL3 should be excluded. In other words I want to find my VAL4-ONLY customers.

       

      Thanks for your help!

       

      Message was edited by: Mounhim Tahtahi. Added a sample report. In the report you will see 7 customers having multiple transactions with a certain transaction type. If I want to show only customers with type1 transactions I get to see customers 10001 until 10006. Because all these customers have a transaction with type1. But I want to see type1-only customers. So in this case I only want to see customers 10001, 10003 and 10006. Hope this clarifies my question.

        • 1. Re: How to filter within a group?
          Matt Lutton

          Can you post a sample packaged workbook (.twbx file)?     You can dummy down any sensitive data.  That'll help ensure you get the help you need. Cheers.

          • 2. Re: How to filter within a group?
            Mounhim Tahtahi

            I have added a packaged workbook with dummy data.

            • 3. Re: How to filter within a group?
              Matt Lutton

              Attached is my attempt, which may not be the most elegant way of doing this--someone else may have a much more effective method.  I did this using a calculated field on the filter shelf.

               

              I wrote a calculation of: SIZE()=1 AND attr([TRANS_TYPE])!="TYPE2" AND attr([TRANS_TYPE])!="TYPE3"

               

              My logic was: Keep only rows that have 1 record and do not include the other TRANS_TYPE values.

               

              I struggled to set up the compute using settings, but it seemed to work with this setup:

              ComputeUsing.png

              The final view is attached (version 8.1 packaged workbook).  I am not sure if this will be easily applied to your production scenario or not as you'll have to set the Addressing/Partitioning correctly for the dimensions in your view, but I wanted to give it a shot anyway.

              • 4. Re: How to filter within a group?
                Mounhim Tahtahi

                Don't think that this will work. It is pure coincidence that the customers who only have TYPE1 transactions have only one transaction. It could be very well that the specific customers have more then 1 TYPE1 transaction.

                 

                You could see it as a report for type of customers. Suppose we have on list of 100 customers. 50 customers use both internet and physical shop as means to buy products, 25 only use internet and 25 only use physical shop. Let's say internet is our Type1.

                In this case I would like to see the 25 internet only customers including their purchases.

                • 5. Re: How to filter within a group?
                  Matt Lutton

                  I understand what you want--I'm just having a hard time getting there today. 

                  • 6. Re: How to filter within a group?
                    Mounhim Tahtahi

                    So have I . Thanks for the effort though. If I find something I will post it here as well.

                    • 7. Re: How to filter within a group?
                      Matt Lutton

                      I'm thinking there's a better solution using sets.  I created a set on CustomerID, with the condition:

                      MAX([TRANS_TYPE])="TYPE1"

                       

                      And filtered for those customers, and got the correct results.  However, I'm again not sure this will apply to your production scenario.

                       

                      Hopefully, someone with more experience and expertise will show us both some best practices for this problem!

                       

                      Screen.png

                      1 of 1 people found this helpful
                      • 8. Re: How to filter within a group?
                        Mounhim Tahtahi

                        That sounds interesting. I will try that tomorrow. It's quite late here now

                        • 9. Re: How to filter within a group?
                          Jonathan Drummey

                          Matthew, MAX([TRANS_TYPE])=="TYPE1" would fail if a customer had a "TYPE0" transaction type or anything else that was lower in the sort order. An alternative would be to use something like ATTR([TRANS_TYPE])=="TYPE1", or building the ATTR yourself by using the formula MAX([TRANS_TYPE])=="TYPE1" AND MIN([TRANS_TYPE])=="TYPE1"

                           

                          Mounhim & Johan, if the data source has more than a few 10s of thousands of records I'd recommend using the Set solution that Matthew posted for performance reasons. Table calculation filters are computed and applied in Tableau, so all the records would be loaded into Tableau, table calculations computed, and then the table calc filters applied. For larger data sets that will slow down views simply because of the time needed to pull the data across the wire into Tableau. Set filters are computed as subqueries in the data source.

                           

                          Also, I suggest reading the order of operations notes in Re: Question on how to create a product velocity calculation., this will help you with situations like this in the future.

                           

                          Jonathan

                          • 10. Re: How to filter within a group?
                            Matt Lutton

                            Thanks for posting this Jonathan.  It is really helpful in terms of understanding the situation in more depth--I had found similar scenarios using MAX conditions inside Sets, but found understanding them a bit difficult--your comments help me understand a bit more about how this works.  Sets are something I don't have a lot of experience with, but I have a feeling I could be using them more often with further knowledge/experience.

                             

                            BTW, I've got that order of operations list pasted on my cubicle wall at work in nice large print... but don't always remember to consider it.

                             

                            Thanks again for your input!