14 Replies Latest reply on Nov 14, 2018 1:42 PM by Joe Oppelt

    Help with a table / filter / calculation.

    Oyin T-O

      I am trying to show client attrition on this table. (Far right column is lost sales).

      I need to exclude any  household names (second column) that contain any portfolios (third column) that do not end in an x.

      Essentially, Carole and Mark should be on the table because all their portfolio codes end with an x but, Mad... should not be on the list because he/she has 2 portfolio codes that do not end in an x.

       

      Is this clear?

      Please help if you have any direction

       

        • 1. Re: Help with a table / filter / calculation.
          Hari Ankem

          Please provide a data file with some dummy data having the same structure as shown above, and your expected result. It will make it easy for someone to suggest the appropriate solution.

          • 2. Re: Help with a table / filter / calculation.
            Joe Oppelt

            { FIXED [Household Billing Group Name] : SUM( IF RIGHT([Portfolio],1) <> "x" then 1 ELSE 0 END) }

             

            This will sum up all the rows under each [Household billing Group Name] that don't have an "x" at the end.  (You'll get a 1 for any that don't have an "x".)

             

            This calc will be set on each row of a given Household.  If this value is >0 then at least 1 of the rows does not have an "x".  So you should exclude those Households.

            1 of 1 people found this helpful
            • 3. Re: Help with a table / filter / calculation.
              Paul Wachtler

              Hi Oyinkansola,

               

              I think you can use an LOD to filter for this.  First create a calculated field called "Include Household" (or something similar):

              {fixed [Household Billing Group Name]: sum( if not(right([Portfolio Co], 1) = "x") then 1 else 0 end)}

               

              That will return a 1 for each household that has at least one value that doesn't end in an x.  Place it on your filter shelf and select "0" only.  That should do it.

               

              Let me know if you have any questions.

               

              Best,

              Paul

              1 of 1 people found this helpful
              • 4. Re: Help with a table / filter / calculation.
                Paul Wachtler

                Ah looks like Joe just beat me to it

                1 of 1 people found this helpful
                • 5. Re: Help with a table / filter / calculation.
                  Chris Chalmers

                  Hey Oyinkansola,

                   

                  Drag [Household Billing Group Name] to the Filters shelf, go to the "Condition" tab, select "By formula", and use this formula:

                   

                  COUNT([Portfolio Code]) == COUNT(IF ENDSWITH([Portfolio Code], 'x') THEN [Portfolio Code] END)

                   

                  Capture.PNG

                   

                  You can also experiment with various kinds of LOD calcs to use this same calculation in other ways:

                   

                  { FIXED [Primary Manager], [Household Billing Group Name] :

                      COUNT([Portfolio Code]) == COUNT(IF ENDSWITH([Portfolio Code], 'x') THEN [Portfolio Code] END)

                  }

                   

                  Note that the above calculate sets the level of detail to Primary Manager + Household Billing Group Name, just in case there can be the same Household Billing Group Names under different managers. To do the same thing in the filter dialog, you would have to filter on a combined field of Primary Manager + Household Billing Group Name.

                   

                  -Chris Chalmers

                  1 of 1 people found this helpful
                  • 6. Re: Help with a table / filter / calculation.
                    Oyin T-O

                    Hi Everyone, Thank you so much for your responses.

                    I have tried all 3 suggestions and I arrive at the same (half incorrect) responses.

                    I used the calculated fields suggested by both Joe Oppelt and Paul Wachtler  as well as the filter suggestion from Chris Chalmers.

                     

                    For some reason, I lose some of the data that should be in there. About 70 % of the household billing group names are excluded (including Carol and Marc from my first screen grab).

                    It is rightfully excluding the household groups that contain a portfolio that does not end in an x, but, it excludes data that should still be in there.

                     

                    Any help?

                    I appreciate it!

                     

                    • 7. Re: Help with a table / filter / calculation.
                      Chris Chalmers

                      It sounds like you have some stuff going on in your data that we can't see from your screenshots. Can you make some dummy data like Hari suggested and send us a workbook demonstrating the problem?

                      • 8. Re: Help with a table / filter / calculation.
                        Joe Oppelt

                        Oyin T-O wrote:

                         

                        Hi Everyone, Thank you so much for your responses.

                        I have tried all 3 suggestions and I arrive at the same (half incorrect) responses.

                        I used the calculated fields suggested by both Joe Oppelt and Paul Wachtler as well as the filter suggestion from Chris Chalmers.

                         

                        For some reason, I lose some of the data that should be in there. About 70 % of the household billing group names are excluded (including Carol and Marc from my first screen grab).

                         

                         

                        I know you are concerned about proprietary data based on your screen shots, but I'm afraid you're not going to find an answer here without a sample workbook we can look at together.  Make a dummy set of data and one sheet to demonstrate your case by following the steps in the video linked here:

                         

                        Video demonstrates how to anonymize your workbook/data

                         

                        The video is about 10 minutes long and it would take you about that long to do the same on your data.

                         

                        All we need for data would be 3 or 4 households with enough portfolio examples to demonstrate all cases.  (One with no "x".  One with some "x".  One with all "x".)  Hack up dummy data.  Toss out all the other rows if you want.  Replace your data source with the excel file of dummy data, and we can work with that.

                         

                        Help us help you.

                        • 9. Re: Help with a table / filter / calculation.
                          Oyin T-O

                          Hi Chris,

                           

                          I have tried to anonymize my data, please see attached.

                           

                          Column c- my calculated field which Paul and Joe provided; You can see that I am getting numbers like 31 and 108 when I assume that number should be 0 or 1.

                          The calculation I have in mind should say something like this=

                           

                          If there is a [portfolio code] within the [Household billing group name] that does not end with x, =1. Indicating there is at least 1 open portfolio and the client isn't completely lost.

                          If all [portfolio codes] within the [Household billing group name] end with x, =0. Indicating all accounts are closed and the client is lost already.

                          Something of the sort so there are only 2 possible results.

                           

                          In the nature of this business, some portfolios may be closed, while others are open ( rows 45 - 81) mad... Group shows a client who has closes 1 portfolio but, has another op

                          meanwhile, Carole...Group has all portfolios closed (they all end in x) so, the filter needs to recognize this.

                           

                          Thanks if you can help! I hope you can.

                          If anyone is in the Toronto area, and willing to train on tableau, please let me know.

                          • 10. Re: Help with a table / filter / calculation.
                            Joe Oppelt

                            Upload the twbx file, not the excel file.

                            • 12. Re: Help with a table / filter / calculation.
                              Joe Oppelt

                              See Sheet 3 in the attached.

                               

                              Because both Portfolio Codes for "Carole" have "x", I get a value of zero.

                               

                              For "Mad" I get a value of 28.  That's correct.  There are 28 rows in the data with the non-"x" Portfolio.  (The FIXED LOD I suggested acts on all rows within the Household.  The SUM of all those 1s is 28.  That's why in my original post I said if the value is greater than 0 than at least one row has a non-"x" Portfolio.  So you want to filter on this calc = 0 to get those households with all "x" portfolios.

                              • 13. Re: Help with a table / filter / calculation.
                                Oyin T-O

                                Wow, First of all thank you for your patience and help with my first community question. It's been SO helpful for a new tableau user.

                                Okay so I think I figured out why this calculation is not capturing ALL the household groups with closed portfolios from your write up. Because the data contains snapshots of when the portfolio was a non-x, as well as when the portfolio gets closed and becomes a __x, it is excluding all the portfolios that have this scenario. So, it's counting 28 for Mad... group because of that.

                                I don't think this formula will work in this case.

                                 

                                New Proposed solution;

                                If you take a look at the workbook, Investment goal is always "closed" when the portfolio is closed. Can you help me with a formula to say

                                " If ALL portfolios within the household billing group have the investment goal - closed, assign value of CLOSED. If at least 1 portfolio within the household billing group is NOT closed, assign value of Open".

                                 

                                Does this make sense?

                                If it works, then we should only see Carole's group as having a closed relationship and Mad's as open.

                                • 14. Re: Help with a table / filter / calculation.
                                  Joe Oppelt

                                  I'm not clear about the problem.

                                   

                                  Because my calc for Carole has a value of zero, that indicates a closed relationship already.

                                   

                                  Mad group has 28.  Even though 185x is closed, we have a value greater than zero, meaning something under Mad group still has something open.  the calc found 28 rows of 19990 without an X, so we know something is still open.

                                   

                                  You could change my calc to look for [Investment Goal] <> "Closed", but the same logical result would happen.

                                   

                                  It looks like [As of Date] is the differentiator for the 28 rows under 19990.  You have 28 different dates there.  That's why we get a value of 28.  It doesn't matter if we get 1 or 100 for the value.  if it's greater than zero, somewhere under that household is a Portfolio that doesn't end in an X.

                                   

                                  I made Calculation2 to assign "Open" or "Closed" to each household.  It's based on Calculation1.

                                   

                                  See attached.