Knowledge Drop

Custom filter: filter the last day of each month/quarter until yesterday

  • 7 July 2021
  • 0 replies
  • 27 views

Userlevel 2
  • Looker Staff
  • 13 replies

Last tested: Sep 3, 2020
 

Some users might have cumulative data ( such as running total partition by month/quarter ) on monthly or quarterly basis, and usually it's quite hard to query the sum of each month/quarter because there's no existing filter option to filter the last day of each month/quarter with a very simple filter expression. However, custom filter might help in this case:

Last day of each quarter until yesterday:

(coalesce(

if(extract_months(add_days(1, ${orders.created_date}))<=3,1,null),

if(extract_months(add_days(1, ${orders.created_date}))<=6,2,null),

if(extract_months(add_days(1, ${orders.created_date}))<=9,3,null),

if(extract_months(add_days(1, ${orders.created_date}))<=12,4,null),

null

)=coalesce(

if(extract_months(${orders.created_date})<=3,1,null),

if(extract_months(${orders.created_date})<=6,2,null),

if(extract_months(${orders.created_date})<=9,3,null),

if(extract_months(${orders.created_date})<=12,4,null),

null

)+1

OR extract_years(add_days(1, ${orders.created_date}))=extract_years(${orders.created_date})+1

OR ${orders.created_date}=add_days(-1, trunc_days(now()))

)

AND

${orders.created_date}<now()

 

This is the logic:

(quarter_number of (date+1) = (quarter_number of date) +1

OR

year of (date+1) = (year of date)+1

OR

date = yesterday)

AND

date<now

 

Last day of each month until yesterday

(extract_months(add_days(1, ${orders.created_date}))=extract_months(${orders.created_date})+1 

OR extract_years(add_days(1, ${orders.created_date}))=extract_years(${orders.created_date})+1

OR ${orders.created_date}=add_days(-1, trunc_days(now()))

)

AND ${orders.created_date}<now()

 

This is the logic:

(month_number of (date+1) = (month_number of date) +1

OR

year of (date+1) = (year of date)+1

OR

date = yesterday)

AND

date<now

 

 

This content is subject to limited support.                

 

 


0 replies

Be the first to reply!

Reply