How to check for consecutive monthly buys

datalady
Registered Posts: 1 ✭
i have a dataset that has purchasing history for many items for the past 10 years, i want to pull out only the items that have been purchased every month for the last 10 years. how do i go about this?
Operating system used: macos
Tagged:
Best Answer
-
Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 2,321 Neuron
There is probably many ways of doing this but I would do it this way. First create a list of all the months / years combination for the last 10 years. Say that gives you 120 rows. Then group your data by product, count distinct(month/year), sum(purchase_amount) where count distinct(month/year) = 120. Then you can use the list of prodcts to filter other datasets.