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

Knowledge Drop

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.                

Version history
Last update:
‎07-07-2021 01:12 PM
Updated by: