Scenario: Saved search formula to calculate the difference percentage of Month to Date Sales this year in comparison to last year.
Solution:
List> Search> Saved Searches
Select Transactions
Enter Search Title
Criteria:
Type: Sales Order, Return Authorization
Main line: False
Tax line: False
COGS: False
Shipping: False
Results:
Field: Documents number, Summary Type: Group
Field: Formula(Currency): Summary Type: (Sum):
(NVL(SUM((((CASE WHEN {type}=’Sales Order’ AND TO_CHAR({trandate}, ‘YYYY’) = TO_CHAR({today}, ‘YYYY’) AND TO_CHAR({trandate}, ‘MM’) = TO_CHAR({today}, ‘MM’) THEN NVL({amount},0)+NVL({taxamount},0) ELSE 0 END)-ABS(CASE WHEN {type}=’Return Authorization’ AND TO_CHAR({trandate}, ‘YYYY’) = TO_CHAR({today}, ‘YYYY’) AND TO_CHAR({trandate}, ‘MM’) = TO_CHAR({today}, ‘MM’) THEN NVL({amount},0)+NVL({taxamount},0) ELSE 0 END)))-(((CASE WHEN {type} = ‘Sales Order’ AND TO_CHAR({trandate}, ‘YYYY’) = TO_CHAR({today}, ‘YYYY’) – 1 AND TO_CHAR({trandate}, ‘MM’) = TO_CHAR({today}, ‘MM’) AND TO_CHAR({trandate}, ‘DD’) <= TO_CHAR({today}, ‘DD’) THEN NVL({amount},0)+NVL({taxamount},0) ELSE 0 END)-ABS(CASE WHEN {type}=’Return Authorization’ AND TO_CHAR({trandate}, ‘YYYY’) = TO_CHAR({today}, ‘YYYY’) – 1 AND TO_CHAR({trandate}, ‘MM’) = TO_CHAR({today}, ‘MM’) AND TO_CHAR({trandate}, ‘DD’) <= TO_CHAR({today}, ‘DD’) THEN NVL({amount},0)+NVL({taxamount},0) ELSE 0 END)))),0)/NULLIF(SUM(((CASE WHEN {type} = ‘Sales Order’ AND TO_CHAR({trandate}, ‘YYYY’) = TO_CHAR({today}, ‘YYYY’) – 1 AND TO_CHAR({trandate}, ‘MM’) = TO_CHAR({today}, ‘MM’) AND TO_CHAR({trandate}, ‘DD’) <= TO_CHAR({today}, ‘DD’) THEN NVL({amount},0)+NVL({taxamount},0) ELSE 0 END)-ABS(CASE WHEN {type}=’Return Authorization’ AND TO_CHAR({trandate}, ‘YYYY’) = TO_CHAR({today}, ‘YYYY’) – 1 AND TO_CHAR({trandate}, ‘MM’) = TO_CHAR({today}, ‘MM’) AND TO_CHAR({trandate}, ‘DD’) <= TO_CHAR({today}, ‘DD’) THEN NVL({amount},0)+NVL({taxamount},0) ELSE 0 END))),0))*100
Label: Difference %
Click Save
Note: Here we have considered only sales orders and return authorizations to calculate.