Rolling Count 7 and 30 days - Line Chart

[nbazz]

Hello,

I have a table with the following fields: date | user id | action done. I want the output to be (but in a line chart) :

Date	Users
2021-10-05	Count of distinct users from 2021-09-29 to 2021-10-05
2021-10-04	Count of distinct users from 2021-09-28 to 2021-10-04
2021-10-03	Count of distinct users from 2021-09-27 to 2021-10-03

Basically, I want to do a rolling count. Above is the rolling count for the last 7 days, but I would need to do the same for the last 30 days also (keeping it in days, not - 1 month).

I read I can do this throught the script, but I didn't understand how. Since I'm still a begginner, I couldn't really wrap my head around the master calendar code.

To do so I absolutely need to use a master calendar or can I do this only using the date field (YYYY-MM-DD) I currently have? How could I do this so I can use rolling counts?

[abhijitnalekar]

Hi @nbazz ,

Use the below condition against date in your expression.

Date={"$(='>=' & Date(ReloadTime(),'YYYY-MM-DD') & '<=' & Date(ReloadTime()-7,'YYYY-MM-DD'))"} 

[nbazz]

Sorry @abhijitnalekar , I didn't specify I want it in a line chart. 

[abhijitnalekar]

Hi @nbazz ,

What will be the dimension of the Line chart. 

[nbazz]

Hi @abhijitnalekar ,

The dimension would be the date (daily, DD/MM/YYYY).

[abhijitnalekar]

Hi @nbazz ,

Please check the below screenshot. I hope you are looking at the same.

Dimension used in the chart is : InvoiceDate

and Measure is : sum({<InvoiceDate={"$(='<=' & Date(max(InvoiceDate),'YYYY-MM-DD') & '>=' & Date(max(InvoiceDate)-30,'YYYY-MM-DD'))"} >}[#TransactionCount])