# How can I make a formula that counts me in a 12 months rolling?

Hi,

I have a dataset with the fields customer and date.
I can build a formula for distinct costumers and show for each month, but I’d like to make a formula to calculate the customers who have purchased at least once in the last 12 months, from the month I am showing,

I tried with runningsum but I can’t achieve it.

Thanks

Hi @KLIKIN,
did you check

BR

Yes, I’ve checked, but How can I set the 12 month rolling in this formula?

Maybe with an

``````ifelse(datediff(now(),{month},"MM",)<=12,runningSum,0)
``````

But with that formula is only for the current month, but If I want to use distinct_count for March-23 where the range is from March-23 to Feb-22 doesn’t work. I tried to adapt with:

ifelse(
runningSum(distinct_count({customerId}),[truncDate(“DD”,{date}) ASC])
,0)

but there is an error message:

Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.

I’ll try to fix it changing the fields of the formula but it doesn’t work

No, I think this function is not for moving date ranges

so actually you dont want a running sum rather then for each month the sum of the last 12 month?

Sorry if I explained myself wrong, what I mean is that I need to show by month the number of different users that there have been in that month and in the previous 11; in such a way that I start on November 23 measuring users from November 23 to December 22 and I end, for example, on January 23 measuring January 23 to February 22

Hi @KLIKIN

first convert the date string into a Date:

Then use windows function to get the partition you want by date:

where 11 stands for the previous 11 months, and 1 stands for the row month. Change it as you deem fit.

This is the result:

I changed the field headers for simplicity and easy of use.

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!

Hi, thanks for your answer, with this solution counts all the customers but not the different or unique customers. Can you think of any other solution?