We In Dashboard Configuration>Categories, we have added a date picker as a category object.
...
We want this to be able to both filter and change the level of aggregation within our charts So when <7 day range is picked then it using this category object.
If we use the date picker to specify a <7 day range, the charts in our dashboard will show individual days,.
...
If we use the date picker to specify a > 7 < 32 it will group range, the charts in our dashboard will be grouped by weeks.
...
And when a period greater than 32 days it’ll group is specified, the charts will be grouped by months.
...
To Achieve this, (This this example is on a PosgresDB – but could be adapted for your own SQL dialect)First created , first create a measure:
...
It’s select statement contains a subquery which looks at the max and min reported dates returned in the users selected date period – we use the {{}}’s date pickers value [0] and [1] to get the full range in this sub select.
It returns the text either ‘Day’, ‘Week’ or ‘Month’
(SELECT
CASE
WHEN MAX(EVENTS_TIMESPAN."reportedAt")::date-MIN(EVENTS_TIMESPAN."reportedAt")::date < 7 THEN 'Day'
WHEN MAX(EVENTS_TIMESPAN."reportedAt")::date-MIN(EVENTS_TIMESPAN."reportedAt")::date < 32 THEN 'Week'
ELSE 'Month' END
FROM
public.event AS EVENTS_TIMESPAN
WHERE EVENTS_TIMESPAN."reportedAt" BETWEEN '{{Date Filter[0]}}'
AND '{{Date Filter[1]}}'
)
The numbers is red – number can be changed – , or could even be [[variabalized]] to be different for different tenants.
Then this This object is then referenced in our date dimension
...
It’s select clause looks like
...
CASE
WHEN #~Max Days Between Reported~# = 'Month' THEN TO_CHAR(Event."reportedAt",'Mon YYYY')
WHEN #~Max Days Between Reported~# = 'Week' THEN TO_CHAR(DATE_TRUNC('week',Event."reportedAt"),'dd Mon YYYY')
||' - '||
TO_CHAR(DATE_TRUNC('week',Event."reportedAt")+ '6 days'::interval,'dd Mon YYYY')
ELSE TO_CHAR(Event."reportedAt",'dd Mon YYYY')
END
This changes the level of aggregation depending on the outcome of the measure object.
It has an associated order by
CASE
WHEN #~Max Days Between Reported~# = 'Month' THEN TO_CHAR(Event."reportedAt",'YYYY-MM') WHEN #~Max Days Between Reported~# = 'Week' THEN TO_CHAR(DATE_TRUNC('week',Event."reportedAt"),'YYYY-MM-DD')
ELSE TO_CHAR(Event."reportedAt",'YYYY-MM-DD')
END