Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Colleagues,
Id like to create a chart which dimensions are based on a calendar variable. The dimensions should show the IDs that happened from the chosen date, 30 days before the chosen date and so on..
ID | Date |
---|---|
1 | 01/12/2017 |
2 | 01/11/2017 |
3 | 11/10/2017 |
4 | 12/09/2017 |
Let´s suppose that we chose the day 19/12/2017.
The dimensions should show then "19/12/2017, 19/11/2017 ... 19/12/2016" (13 dimension values).
The expression should be a simple count distinct ID.
Can someone help me out?
Thx!
I think all your need is set analysis in your expression
Dimension
Date
Expression
Count(DISTINCT {<Date = {"$(='>=' & Date(Max(Date) - 30, 'MM/DD/YYYY') & '<=' & Date(Max(Date), 'MM/DD/YYYY'))"}>} ID)
Also, look here
Sunny, how many dimensions will I have in the chart you are proposing?
I mean.. how many values in the dimension axis.
It should show all available dates between max date and max date - 30
Ok, as I checked.. But the aim is to create 12 dimesion values.
The first one would show values between max date and max date-30, the second one shows between max date-30 and max date -60 and so on..
Then you can either create a new dimension in the script or you can use a calculated dimension
If(Date > Max(TOTAL Date) - 30 and Date <= Max(TOTAL Date), 'max date - max date - 30',
If(Date > Max(TOTAL Date) - 60 and Date <= Max(TOTAL Date) - 30, 'max date - 30 - max date - 60',
and so on....