Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi @nbazz ,
Use the below condition against date in your expression.
Date={"$(='>=' & Date(ReloadTime(),'YYYY-MM-DD') & '<=' & Date(ReloadTime()-7,'YYYY-MM-DD'))"}
Sorry @abhijitnalekar , I didn't specify I want it in a line chart.
Hi @nbazz ,
What will be the dimension of the Line chart.
Hi @abhijitnalekar ,
The dimension would be the date (daily, DD/MM/YYYY).
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])