- Trimester 1 – February 1 to May 31
- Trimester 2 – June 1 to September 30
- Trimester 3 – October 1 to January 31
- Create a saved search of type transaction
- Criteria-
Type is Invoice
Created From : Type is Sales Order
Paying Transaction : Type is any of Deposit Application, Payment
Created From : Date is within this fiscal year
Main Line is true
Created From : Status is not Sales Order: Cancelled
Result
For year – Formula (Text) -Group- ‘FYE ‘ || CASE WHEN TO_CHAR({createdfrom.trandate}, ‘MM’) = ’01’ THEN TO_CHAR({createdfrom.trandate}, ‘YYYY’) – 1 else TO_CHAR({createdfrom.trandate}, ‘YYYY’) – 0 END
For trimester – Formula (Text)- Group- CASE WHEN TO_CHAR({createdfrom.trandate},’MM’) in (’02’,’03’,’04’,’05’) THEN ‘TRIMESTER1′ WHEN TO_CHAR({createdfrom.trandate},’MM’) in (’06’,’07’,’08’,’09’) THEN ‘TRIMESTER2′ WHEN TO_CHAR({createdfrom.trandate},’MM’) in (’10’,’11’,’12’,’01’) THEN ‘TRIMESTER3’ END
Sales order number – Created From : Document Number – Group
Invoice number – Document Number – Group
Invoice amount in (CAD)- Formula (Currency) Sum MAX(case when {type} = ‘Invoice’ then {amount} END)
Payment amount in (CAD)- Formula (Currency) Sum Absolute Value CASE WHEN {payingtransaction.type}= ‘Payment’ THEN {payingamount} END
Customer deposit amount in (CAD)- Formula (Currency) Sum Absolute Value CASE WHEN {payingtransaction.type}= ‘Deposit Application’ THEN {payingamount} END
Cash collected in (CAD) – Formula (Currency) Sum ABS(NVL(CASE WHEN {payingtransaction.type}= ‘Payment’ THEN {payingamount} END,0)) + ABS(NVL(CASE WHEN {payingtransaction.type} = ‘Deposit Application’ THEN {payingamount} END, 0))
Available filters
Date
