In general, LOD would help you achieve the requirements.
However, solution might be different depending on the data format.
So please attach a workbook or some sample data, for more specific advices.
Besides, I have two questions about the requirements.
Radu Manoil wrote:
So basicaly I have a product A and a product B. I need to calculate the no of customers that have bought product A on a monthly basis along with product B min one in the last 2 months.
What's the definition of "on a monthly basis"?
Does it mean the customer phrases product A each month from the earliest date to the latest date in your data?
If the customer didn't purchase product A in any of the months, the customer will not be counted?
What's the definition of "last 2 months"?
Is it a relative date range from today or any selected month?
Thank you for picking this up Lei!
Please find attached an example sheet. At the moment the calculation is showing the no of customers that have bought both products in one month. I can see this at customer level and also at SR and SM level ( no of customers from each SR or SM area that meet the condition)
Now what i want:
For example In June customer C1 have bought product A but not B. However because B was bought in the last 2 months I need the customer to be counted also.
So for the condition to be true product A needs to be bought in the current month but B can be bought in current or previous month (current month -1). If outside current month -1 then not to be counted.
Hope it clarifies what I'm after.
Book1.twbx 20.0 KB
Please refer to the attached workbook for details.
I don't think this solution is good because several table calculations are used, which makes it somewhat confusing.
However I haven't go any other idea for the moment
So please first have a look at the workbook to see if it meets your requirement.
Book1_v10.2.twbx 28.0 KB