Filter from 3 months rolling revenue

Question & Answer
, ,
1

I have build a visual for calculating 3 months rolling revenue and and I want to count customers who had 3 rolling revenue more than 1000
How can I achieve this?

2

Hello @Tina1, my initial thought here will be to utilize a sumOver function to get the monthly revenue per cust. With the LAC-W function, we are able to partition the calculation. It would look something like this:
Cust Revenue Per Month = sumOver({revenue}, [truncDate('MM', {Date}), {customer}], PRE_AGG)

You could even use a similar function, but rather countOver or distinctCountOver to figure out the number of time revenue was gained by custs each month.

Now, depending on aggregation issues with ifelse statement, we want to return custs where the above function is greater than 1000 for each month.

ifelse({Cust Revenue Per Month} > 1000, {customer}, NULL)

This should at least give you a way to determine if the cust hit the monthly required goal. So now, if their name is returned for each of the 3 months, you would want to count the cust that meet the requirement. If you have any further questions, please let me know!