The key expression in the formula is:
- {transaction.trandate} BETWEEN TRUNC(ADD_MONTHS({today},-11), ‘Month’) AND TRUNC(ADD_MONTHS({today}, 1), ‘Month’)-1
Allow me to break it down:
- (A) TRUNC(ADD_MONTHS({today},-11), ‘Month’) returns the 1st day of the month, 11 months ago from today. So if today is Feb. 14, 2024, it returns Mar. 1, 2023.
- (B) TRUNC(ADD_MONTHS({today}, 1), ‘Month’)-1 returns the last day of the current month. So if today is Feb. 14, 2024, it returns Feb. 29, 2024.
- Therefore, the formula {transaction.trandate} BETWEEN (A) AND (B) is evaluated as: {transaction.trandate} BETWEEN Mar. 1, 2023 AND Feb. 29, 2024.
- Since the reference date is {today}, then the value for (A) and (B) formulas will also change dynamically. Meaning if you run the search on March 1, 2024, the dates will now change to BETWEEN April 1, 2023 and March 31, 2024.
- Some application of this can be as below
- For 1 location only: CASE WHEN {transaction.type} IN (‘Cash Sale’, ‘Invoice’, ‘Credit Memo’) AND {transaction.location} = ‘Location A’ AND {inventorylocation} = ‘Location A’ AND {transaction.trandate} BETWEEN TRUNC(ADD_MONTHS({today},-11), ‘Month’) AND TRUNC(ADD_MONTHS({today}, 1), ‘Month’)-1 THEN {transaction.quantity} ELSE 0 END
- For 2 locations: CASE WHEN {transaction.type} IN (‘Cash Sale’, ‘Invoice’, ‘Credit Memo’) AND {transaction.location} = ‘Location A’ AND {inventorylocation} = ‘Location A’ AND {transaction.trandate} BETWEEN TRUNC(ADD_MONTHS({today},-11), ‘Month’) AND TRUNC(ADD_MONTHS({today}, 1), ‘Month’)-1 THEN {transaction.quantity} ELSE 0 END + CASE WHEN {transaction.type} IN (‘Cash Sale’, ‘Invoice’, ‘Credit Memo’) AND {transaction.location} = ‘Location B’ AND {inventorylocation} = ‘Location B’ AND {transaction.trandate} BETWEEN TRUNC(ADD_MONTHS({today},-11), ‘Month’) AND TRUNC(ADD_MONTHS({today}, 1), ‘Month’)-1 THEN {transaction.quantity} ELSE 0 END