found that date formula i was looking for: CASE WHEN ({transaction.trandate} >= TRUNC({today} - 7 ,'IW') and {transaction.trandate} <= NEXT_DAY({today} - 7 ,'SUNDAY'))