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:

  1. Create a new filter and give it a name such as custom_aggregation
  2. Select "Type in Names and Values"
  3. Insert the following options:                                                                                               
  4. 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:

[date_field=custom_aggregation]
Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
Like5 Follow
  • 5 Likes
  • 7 mths agoLast active
  • 1127Views
  • 1 Following