View the original community article here
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.