1 2 Previous Next 16 Replies Latest reply on Apr 30, 2015 2:11 AM by Simon Runc

    How to exclude certain consumers from total count?

    Mira Shen

      Data:

       

      consumer, category, product, issue

       

      one parameter "group1" which is simply “product”

      one field “compare group” which is

       

      (IF [Product] = [group1] then "compare1"

       

      ELSE "compare2"

       

      END)

       

       

       

      I am trying to get the consumer count for P1 (compare1 under compare group)and the rest of products(compare2 under compare group). Consumer 11 involved with TWO products, one of which is P1. Since I’ve counted consumer 11 under p1, I’d like to exclude consumer 11 from the rest of products (compare2). As shown in the second screenshot, I want the total for “compare2” to be 14 (excluding case 11) instead of 15 (currently including case 11).

       

        • 1. Re: How to exclude certain consumers from total count?
          Simon Runc

          hi Mira,

           

          I think you can solve this with a clever use of sets

           

          The exact details on how this works can be found in the excellent video on Sets on Think Data Thursday

          Think Data Thursday - November 20 - Let's talk about Sets Baby!

           

          What I have done is create a set on Consumer using the condition

           

          MAX( IIF([Product UPPER] = [Selected Product], 1, 0 ) ) = 1

           

          (btw I've created a Product UPPER field as there was P1 and p1!)

           

          This has the effect of putting any consumers who purchased from the selected product into the group, and everyone else outside.

           

          I've then brought this set on to the Column Shelf, and used the Alias feature to rename In to Compare 1 and Out to Compare 2

           

          Hope this is what you were after. If not, or you want a bit more detail on how it works please post back

          1 of 1 people found this helpful
          • 2. Re: How to exclude certain consumers from total count?

            Hi, Simon,

             

            Thank you so much!

             

            It is what I want at this point. How did those "compare1 " and "compare2" show up under "IN/OUT of consumer who purchased selected product"? Can I change the name? Like "In" and "Out"?

             

            Thanks.

            Mira

            • 3. Re: How to exclude certain consumers from total count?
              Simon Runc

              Hi Mira,

               

              Excellent!

               

              Yes Tableau has alias'. If you right click in the column headings (where I

              have Compare 1, Compare 2), you'll see an option called edit 'Alias', where

              you can change how the In/Out is displayed

               

              On 20 April 2015 at 15:06, Guest <tableaucommunity@tableausoftware.com>

              • 4. Re: How to exclude certain consumers from total count?

                Got it! Thank you so much for your help!

                 

                Best,

                Mira

                • 5. Re: How to exclude certain consumers from total count?

                  Hello, Simon,

                   

                  Just noticed that the case count for each issue wasn't correct when it involved with multiple products.

                   

                  case          product     issue

                  --------------------------------------

                  Case 11      P1            bad4

                  Case 11      P3            bad3

                   

                  Once In/Out condition was applied, we moved "bad3" to Comparer1(P1) which was supposed to be in Comparer2 (other products).

                   

                  I tried playing with this but didn't work it out. Can you help?

                   

                  Thanks.

                  Mira

                  • 6. Re: How to exclude certain consumers from total count?
                    Simon Runc

                    hi Mira,

                     

                    I think I see the issue. It's actually each Consumer/Case combination that needs to be compared.

                     

                    Have a look at the attached, and let me know if this isn't what you mean. I've done it in exactly the same way, but have created a new Customer_Case dimension

                     

                    [Consumer]+'_'+[Issue]

                     

                    And built the set and the conditioning on this.

                    1 of 1 people found this helpful
                    • 7. Re: How to exclude certain consumers from total count?
                      Mira Shen

                      Hi, Simon,

                       

                      Thank you! This goes back to the default in which the total of comparer2 was 15! (I want 14).

                       

                      Thanks.

                      Mira

                      • 8. Re: How to exclude certain consumers from total count?
                        Simon Runc

                        hi Mira,

                         

                        I'm sure there is a way. Can you just confirm what we are trying to get to. Below is a table showing for each row, where it is classified (in terms of Compare 1 and Compare 2), based on the selected Product being P1.

                         

                        The 1st In/Out column is the original solution, which puts Consumers into Compare 1 if they have and issue with P1 (regardless of if they also has an issue with a second product).

                         

                        The 2nd In/Out column put them into Compare 1 on an issue by issue/product basis.

                         

                        So for Case 11 in the first one, both their issues with P1 and P3 go into Compare 1 (as they have purchased P1), and the second version they (case 11) appear in both compare groups, their P1 issue goes into Compare1, and their P3 issue is in Compare 2

                         

                           

                        ConsumerCategoryProductIssueIn / Out of Consumers Who Purchased XIn / Out of Consumer_CaseID Who Didn't Purchase X
                        Case1Ap1bad1Compare 1Compare 1 New
                        Case2Ap2bad2Compare 2Compare 2 New
                        Case3Ap3bad1Compare 2Compare 2 New
                        Case4Ap4bad3Compare 2Compare 2 New
                        Case5Ap5bad1Compare 2Compare 2 New
                        Case6Ap1bad1Compare 1Compare 1 New
                        Case7Ap6bad4Compare 2Compare 2 New
                        Case8Ap7bad1Compare 2Compare 2 New
                        Case9Ap1bad5Compare 1Compare 1 New
                        Case10Ap1bad2Compare 1Compare 1 New
                        Case11AP1bad4Compare 1Compare 1 New
                        Case11Ap3bad3Compare 1Compare 2 New
                        Case12Ap4bad2Compare 2Compare 2 New
                        Case13Ap6bad1Compare 2Compare 2 New
                        Case14Ap7bad5Compare 2Compare 2 New
                        Case15Ap5bad2Compare 2Compare 2 New
                        Case16Ap3bad4Compare 2Compare 2 New
                        Case17Ap1bad3Compare 1Compare 1 New
                        Case18Ap2bad1Compare 2Compare 2 New
                        Case19Ap3bad1Compare 2Compare 2 New
                        Case20Ap7bad1Compare 2Compare 2 New

                         

                        If you can let me know what should be in Compare 1 and 2 in  this example and I'm sure we can come up with the solution

                        • 9. Re: How to exclude certain consumers from total count?
                          Mira Shen

                          Hi, Simon,

                           

                          Thank you so much for working on this and sorry for the confusion.

                           

                          The challenge is:

                           

                          For individual issue, we want the case count following the last column of your table

                          For grand total, we want the case count following the last second column of your table.

                           

                          what we want from the sample data is:

                             

                           

                          IssueCompare 1Compare 2Grand Total
                          bad1279
                          bad2134
                          bad3123
                          bad4123
                          bad5112
                          Grand Total61420

                           

                          The problem is we either got the case count for individual issue desired

                          (for bad 3: 1 and 2 for comparer1 and comparer2 respectively) but an unwanted total (15 for comparer2)

                           

                          Issue Compare 1 Compare 2 Grand Total
                          bad1 2 7 9
                          bad2 1 3 4
                          bad3 1 2 3
                          bad4 1 2 3
                          bad5 1 1 2
                          Grand Total 6 15 21

                           

                           

                          got the total case count desired (15 for comparer2)

                          but unwanted case count for individual issue (for bad 3: 2 and 1 for comparer1 and comparer2 respectively)

                           

                          Issue Compare 1 Compare 2 Grand Total
                          bad1 2 7 9
                          bad2 1 3 4
                          bad3 2 1 3
                          bad4 1 2 3
                          bad5 1 1 2
                          Grand Total 6 14 20

                           

                          I searched online and found a discussion on Customizing Grand Total at Customizing Grand Totals – Part 2 | Drawing with Numbershttp://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/

                          but I even cannot identify my grand total row using MIN(Issue)!=MAX(Issue) because the value was blank for Grand Total Row (all others are false).

                           

                          Thanks.

                          Mira

                          • 10. Re: How to exclude certain consumers from total count?
                            Simon Runc

                            hi Mira,

                             

                            Yes that is a fairly complicated problem, however (and I'm thankful you are on Tableau 9.0!) have manged to solve with the new LoD calculations. It feel's a bit contrived! but it works....

                             

                            First thing to say is you are exactly on the right lines with the link you looked at, so good researching! There is also a Think Data Thursday on the subject 

                            TDT Video Library

                            about half way down, the one with Jonathan Drummey

                             

                            ...So to the solution (I've also detailed out the steps in separate calculations, but you could nest your final version in one)

                             

                            First calculation I created was, Consumer Case Products, with the formula

                             

                            {FIXED [Consumer]: COUNTD([Product UPPER])}

                             

                            This puts, against every row (regardless of the view) the number of Products for each case (this way I can identify, any consumer with more than one product...btw You can see how all these calculations equate in the 'Data' tab at row level)

                             

                            The next one is to use this to create a new field which only contains the [Consumers] with more than one Product. This field is called, Consumer (with > 1 Prod), and has the calc

                             

                            IIF([Consumer Case Products]>1,[Consumer],NULL)

                             

                            So now I can minus the COUNTD of this from the Grand Total row, and use the previous 'correct' part to populate the table, and my new field to remove (one in this case) from the GT!

                             

                            So I've created the GT field, very much as per the link you were looking at. The field is called 'Consumer Count with GT'

                             

                            IF MIN([Issue])=MAX([Issue]) THEN COUNTD([Consumer])

                            ELSE COUNTD([Consumer Case ID])-COUNTD([Consumer (with > 1 Prod)])

                            END

                             

                            and we get to the result you wanted.

                            1 of 1 people found this helpful
                            • 11. Re: How to exclude certain consumers from total count?
                              Mira Shen

                              Hi, Simon,

                               

                              Thank you for being so responsive and responsible! I haven't had a chance going through it but it looks very promising! I will let you know.

                               

                              I have another question and wonder if you can help out? I tried sets and combined sets but I didn't work.

                              http://http://community.tableau.com/thread/164435?q=how to calculate the average

                               

                              Tons of thanks.

                              Mira

                              • 12. Re: How to exclude certain consumers from total count?
                                Alexander Mou

                                Please close this question by marking Simon's answer as correct.

                                • 13. Re: How to exclude certain consumers from total count?
                                  Mira Shen

                                  Hi, Simon,

                                   

                                  I just went through your workbook. It's almost perfect except the grand total for Compare1New was 5 but not 6. .

                                   

                                  I want 6 as below.

                                   

                                  IssueCompare 1Compare 2Grand Total
                                  bad1279
                                  bad2134
                                  bad3123
                                  bad4123
                                  bad5112
                                  Grand Total61420

                                   

                                  I tried to incorporate one more condition for SET VARIABLE in IF but no luck.

                                   

                                  Thanks.

                                  Mira

                                  • 14. Re: How to exclude certain consumers from total count?
                                    Simon Runc

                                    hi Mira,

                                     

                                    This problem is a bit like plugging a leaky dam...once you plug one hole a new one crops up!!

                                     

                                    However I have come up with a different solution, which does get us back to the table you've posted.

                                     

                                    This method splits out the fields into 3 fields. One for Compare 1, One for Compare 2, and one for the issue total. It's getting a little messy, and pretty bespoke to this exact problem, but hopefully you can take the techniques to your more general problem.

                                     

                                    What I have done is create a calculated field, which only contains the Customers in Compare 1. I've called this 'Customer Compare 1' and has the following formula

                                     

                                    IIF([Consumer_CaseID Who Didn't Purchase X],[Consumer],NULL)

                                     

                                    I bring this field in as a COUNTD (and let the natural Tableau Column Total create the Grand Total). We're taking advantage here of the fact the Tableau (and in fact any program) can't count NULLs

                                     

                                    I've then created a 'customer compare 2' field in a similar way

                                     

                                    IIF(NOT([Consumer_CaseID Who Didn't Purchase X]),[Consumer],NULL)

                                     

                                    but for this one I want to 'adapt' the way Tableau naturally would create the Grand Total, so I have then created a new field, called 'Consumer Compare 2 GT' with the formula

                                     

                                    IF MIN([Issue])=MAX([Issue]) THEN COUNTD([Consumer Compare 2])

                                    ELSE COUNTD([Consumer Case ID Compare 2])-COUNTD([Consumer (with > 1 Prod)])

                                    END

                                     

                                    As you'll notice there is another field in here, called  'Consumer Case ID Compare 2'. This is the same as the previous, 'consumer case ID' field we created before ([Consumer]+'_'+[Issue]), but only brings back this ID if the customer is in Compare 2, so has the formula


                                    IIF(NOT([Consumer_CaseID Who Didn't Purchase X]),[Consumer Case ID],NULL)

                                     

                                    To get the Grand total for Rows, I've actually created another field and brought this in (so not using Tableau Row Grand Totals). This field is called 'Issue Total with GT', with the formula

                                     

                                    IF MIN([Issue])=MAX([Issue]) THEN COUNTD([Consumer Compare 1])+COUNTD([Consumer Compare 2])

                                    ELSE COUNTD([Consumer Case ID])-COUNTD([Consumer (with > 1 Prod)])

                                    END

                                     

                                    And we get to your table.

                                     

                                    I appreciate this is a bit of messy solution, but we are asking Tableau to not add up correctly (if you see what I mean!), so I'm happy it's fairly tricky! It does also show the flexibility of the software thought.

                                     

                                    ...you've certainly got me working for my green tick!! although I have learned a lot about controlling Grand Totals myself...which is why I love the community.

                                    1 2 Previous Next