4 Replies Latest reply on Jan 4, 2017 6:48 AM by Walt Reed

    Count distinct for customers with additional product this year

    Hemalatha Dave

      Hi,

       

      Apologies if the heading is misleading, was not sure how to frame the question. Hence posting the input and desired output.

       

      Input is

      tableau help.png

       

      I want to find out the number of customers that added bought additional product in 2015. So in the above example, only Tom and John added new product(s) in 2015. As they had retained  the existing product (from 2014) as well. Any leads on how to do it in tableau? Note that the year is dynamic field in  my data. TIA, Hema

        • 1. Re: Count distinct for customers with additional product this year
          Joe Oppelt

          I would have either a table calc or a LOD calc that evaluates by year.  SUM(Number of rows) by year.  If greater than 1, then the customer bought additional items within that year.

          • 2. Re: Count distinct for customers with additional product this year
            Walt Reed

            Hey Dave,

            I had to make the assumption that you didn't want to count the products that were purchased in the initial year. So if your example above, don't count Product X for Tom for 2014. If that's how you want it, then I was able to accomplish this with a couple calculations. (Or if you want it to count the initial, we can modify the calc.)

            The first calculation looks at the year of the order date compared to the first year that a given product was ordered. But the second part of calculation with the AND statement excludes products that were ordered in the initial year.

            Then, create another formula to count only records that were not marked as "Retained Product":

            Add this field to the viz and SUM it, and you get the desired result:

            Let me know if this accomplishes what you want!

             

            Walt

            1 of 1 people found this helpful
            • 3. Re: Count distinct for customers with additional product this year
              Hemalatha Dave

              Thanks Walt. This was indeed helpful. out of curiosity - If I need only those customers where additional line was added, so basically if a customer had 2 lines in 2014 (Furniture and Technology) and 2 lines in 2015 (Furniture and Office Supplies) then this customer shouldn't have 'New product' (since the customer lost Technology). Had the customer bought Furniture, technology and Office Supplies in 2015 then it should be flagged. Maybe a intermediate calculation might help (not sure). Hope I haven't complicated the question :P

              • 4. Re: Count distinct for customers with additional product this year
                Walt Reed

                Hey Dave,

                Yeah I think you can do this. Can you clarify whether you only want to count customers where a new line was added and exclude all others?

                 

                What about if they had 2 lines in 2014 (Furniture and Technology), nothing in 2015, then 3 lines in 2016 (Furniture, Technology, and Office Supplies). Would you count this?

                 

                There are so many different scenarios that could play out, I just want to make sure you're capturing only what you need.

                 

                Walt