4 Replies Latest reply on Mar 14, 2016 12:20 PM by pooja.gandhi

    Want to filter customers

    ChandraSheker A

      Hi everyone.

       

      I have to filter the customers out in the below data.

      i am attaching a sample sheets exported to excel. please find it.

      My Question is:

      Customers can have any number of IDs.

      I want to see only those customers Who are

      in Payment status: Active, Inactive, paid in full.    but also Payment status: Managed who have a managed status: resolved.

       

      for Example.  if a customer is having a id where payment status is uncollectable, that means that customer is bad even though if he has other ids which are paid in full or active. so i want to exclude this customer from my report.

       

      If you see in Above Pic.

      for customer:  Allen, D.D.S., PLC, Dandridge B      you can see he has few ids with managed-H, managed-innactive etc.....so   i want to exclude this customer even though he has few Paid in full Ids.

      and                   Adrion Reid dba Tru Flavaz          this customer has 2 Id's where he paid in full, but uncollectable in another. so this customer is bad for me and have to exclude him.

      But,

      for above  customer,

      he has ID's with paid in full, but he has an ID which in payment status : managed but it is also Managed status :resolved. That means this customer was bad before but since it is resolved i can consider this customer as good.

       

      So finally i want to see only good customers who never had the Payment statuses as Managed, Managed-h,uncollectable, managed-inactive.     but can be considered good if Managed and resolved.

       

      How can i achieve this i am attaching sample excel.

       

       

       

      let me know if i am not clear.

       

        • 1. Re: Want to filter customers
          ann.stolzman

          ChandraSheker,

           

          How about creating a calculation like this:

          IF [Payment Status] = "Active" THEN "Good"

          ELSEIF [Payment Status] = "Inactive" THEN "Good"

          ELSEIF [Payment Status] = "Paid In Full" THEN "Good"

          ELSEIF [Payment Status] = "Managed" AND [Managed Status] = "Resolved" THEN "Good"

          ELSE "Bad"

          END

           

          You can still add Payment Status and Managed Status as filters, if you want to narrow in, but then you have this other field that you can filter on, or separate ID's by.  That will, at least, get you the grouping for the "good" customers.  Your last requirement - if you want to make sure that the negative Managed Status' aren't part of "Good", then you can adjust the IF statement to categorize them as Bad.

           

          Does that help?

          -Ann-

          • 2. Re: Want to filter customers
            ChandraSheker A

            Thanks,

             

            But that calculation will filter only ID level.

             

            i want to see only customers .

            your calculation would display same customer good and bad,

            jus like below pic.

             

            you can see top customer,

            i want to see him as Bad.

            hope you understand my question.

            • 3. Re: Want to filter customers
              ChandraSheker A

              am i clear to you?

              • 4. Re: Want to filter customers
                pooja.gandhi

                Not sure which version of Tableau you have access to. If it is version 9.0 or higher, that would be a calculated field like this:

                 

                {fixed [Legal Name] : IF

                min({ fixed [Legal Name], [Id] : max(if [Payment Status] = "Active" or [Payment Status] = "Inactive" or [Payment Status] = "Paid In Full" then 'Show'

                elseif [Payment Status] = 'Managed' and [Managed Status] = 'Resolved' THEN 'Show' ELSE 'Hide' end )})

                = 'Hide' then 'Hide' ELSE 'Show' END }

                 

                 

                Drag it to filter and select 'Show' to see all 'Good' customers. Hope this helps!

                1 of 1 people found this helpful