topic Re: Rolling Count 7 and 30 days in App Development

Rolling Count 7 and 30 days - Line Chart

nbazz — Wed, 22 Dec 2021 15:42:54 GMT

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?

Re: Rolling Count 7 and 30 days

abhijitnalekar — Wed, 06 Oct 2021 13:32:02 GMT

Hi @nbazz ,

Use the below condition against date in your expression.

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

Re: Rolling Count 7 and 30 days

nbazz — Fri, 08 Oct 2021 00:54:11 GMT

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

Re: Rolling Count 7 and 30 days

abhijitnalekar — Fri, 08 Oct 2021 07:36:51 GMT

Hi @nbazz ,

What will be the dimension of the Line chart.

Re: Rolling Count 7 and 30 days

nbazz — Fri, 08 Oct 2021 17:29:57 GMT

Hi @abhijitnalekar ,

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

Re: Rolling Count 7 and 30 days

abhijitnalekar — Mon, 11 Oct 2021 06:54:30 GMT

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