Category Object - Dimension Aggregation

Owned by Karen Hogan (Unlicensed)
Last updated: Jul 06, 2022
3 min read

In Dashboard Configuration>Categories, we have added a date picker as a category object.

We want to be able to both filter and change the level of aggregation within our charts 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 range, the charts in our dashboard will be grouped by weeks.

And when a period greater than 32 days is specified, the charts will be grouped by months.

To Achieve this, (this example is on a PosgresDB – but could be adapted for your own SQL dialect), 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 number can be changed, or could even be [[variabalized]] to be different for different tenants.

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