Forum Discussion

hlorosc's avatar
hlorosc
Cloud Apps
12-14-2021

Rolling 30 Calculations: Counts NOT Avg/Sum

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-date-range-on-dashboards--widgets

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)?

Dashboards & Reporting

2 Replies

Replies have been turned off for this discussion
  • harikm007's avatar
    harikm007
    Data Warehouse
    12-19-2021

    Hi hlorosc ,

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

  • hlorosc's avatar
    hlorosc
    Cloud Apps
    12-27-2021

    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