Saved Search > Formula To Capture Transaction Amount If Date Is Within Specified Range

Scenario

User wants to pull a transaction saved search with the following conditions:

1. If the Account is Late Fees, return the actual amount value,
2. If the Account is Rental Income

-- return the actual amount value if the Date is between 6th to 10th of the Month,
-- for the rest of the dates, return 0

Solution

1. Go to Reports > New Search > Transaction > Create Saved Search

2. In Results tab, select Formula (Currency)

3. In the Formula window, set:

CASE WHEN {account} = 'Late Fees' THEN {amount} WHEN {account} = 'Rental Income' THEN (CASE WHEN TO_CHAR({trandate}, 'DD') BETWEEN 6 AND 10 THEN 10 ELSE 0 END) END

     4. Rename the search
    
     5. Click Save&Run

Disclaimer

N/A