Rolling 30 Calculations: Counts NOT Avg/Sum

[hlorosc]

Hi all!

I found this article: https://community.sisense.com/t5/knowledge/rolling-trailing-12-months/ta-p/565

on how to do rolling calculations if you have averages or sums.

Unfortunately, I need to do a rolling 30 day unique count - I want to know the rolling 30 # accounts we have by day...  

Can't do it in SQL/Table level b/c want to be able to filter and get new values (e.g. rolling 30 for account type A vs. rolling 30 for account type B)

Also found this and tried to put count unique in for average (with a range), but it gave me the same date multiple times over and over (so wasn't working): https://www.rapidbi.com.au/members/index.php?/Knowledgebase/Article/View/rolling-average-or-relative...

Anyone have tips/solution on how to get this calculation? Anyone using rolling X at the formula level (rather than the custom column/table level)?

[harikm007]

Hi @hlorosc ,

Have you tried RPSUM([# of unique Name], 30) ? Its working for me.

[hlorosc]

Unfortunately, that gives me the sum of the unique ids each day for 30 days b/c my data set has the same account listed on multiple days (all days it was paying).

I need to get the unique count over the 30 days (so each account is only counted one time, even if it appears on multiple days). I'll keep playing around and see what I can find