Skip to end of banner
Go to start of banner

Category Object - Dimension Aggregation

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Current »

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

 

 

 

 

 

 

  • No labels