Custom Aggregation Filter - Monday Week Start
UPDATE AUGUST 2, 2018
This post is superseded by a new post found here: link.
This post will be archived on August 13, 2018
The week truncation is a great way to get 7 day periods, but picking the start date for the week is sometimes a tricky thing to get aligned with user expectations. One way to make this possible is by creating your own custom aggregation filter to pass either a Sunday start or a Monday start. Here's how to get that set up:
- Create a new filter and give it a name such as custom_aggregation
- Select "Type in Names and Values"
- Insert the following options:
- Under "More Options", make the filter a radio button and have the following in Match on Expression
case when [value] = 'week_monday' then date_trunc('week', [column] + '1 day'::interval)::date - '1 day'::interval when [value] = 'total' then 'total' when [value] is not null then date_trunc([value] ,([column])::timestamp)::date else date_trunc('date' ,([column])::timestamp)::date end
Then, when using the filter, use an equals sign for the date field in the common Periscope filter syntax: