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

Question & Answer
, ,
1

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,

example
example518×542 12.8 KB

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

Thanks

2

Hi @KLIKIN,
did you check

BR

3

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

4

Maybe with an

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

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(
datediff({date},addDateTime(-12,“MM”,{date}),“MM”)<=12,
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

6

What about

7

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

8

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

9

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

10

Hi @KLIKIN

first convert the date string into a Date:

image
image660×546 22.8 KB

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

image
image780×396 14.1 KB

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:

image
image1096×858 41.9 KB

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!

11

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?