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.
1 of 1 people found this helpful
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!
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
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.