sum based on date within a range
Given a week ending date, a transaction date, and an amount, I would like to set the week ending date based on the transaction date. The week ending date will be the Sunday following the transaction date (e.g., if the transaction date is 2/14/2014 the week ending date would be 2/16/2014.
Calculate the sum of the transactions that occurred during the week preceding the week ending date. This could be written as:
IF(WE_Date - 6 ≤ TR_Date ≤ WE_Date; SUM(amount))
I've tried a number of things that don't seem to work. Any suggestions would be greatly appreciated.