Category Object - Dimension Aggregation

We have a date picker as a category object.

We want this to both filter and change the level of aggregation within our charts

So when <7 day range is picked then it will show individual days,

When > 7 < 32 it will group by weeks

And when greater than 32 days it’ll group by months

To Achieve this (This example is on a PosgresDB – but could be adapted for your own SQL dialect)

First created a measure

It’s select 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 – can be changed – or could even be [[variabalized]] to be different for different tenants.

Then this object is 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