User needs to create a Sales Order search to compare amounts in multiple date range which includes this week, same week last year, this month, same month last year, this year to date and last year to date.
Solution
- Navigate to Reports > Saved Searches > All Saved Searches > New
- Click Transaction
- Search Title: Enter Title
- Click Criteria
- Click Standard
- Filter:
- Select Type
- Type: Select Sales Order
- Click Set
- Select Main Line
- Click Yes
- Click Results
- Click Columns
- Click Remove all
- Field:
Note: Click Add after selecting below fields and add other fields as needed.
- Select Name
- Summary Type: Group
- Select Formula (Currency)
- Summary Type: Sum
- Formula: Enter CASE WHEN {trandate} BETWEEN {today}-(to_number(to_char({today}, ‘D’))) AND {today} THEN {amount} ELSE 0 END
- Summary Label: Enter This Week
- Select Formula (Currency)
- Summary Type: Sum
- Formula: Enter CASE WHEN {trandate} BETWEEN {today}-(to_number(to_char({today}, ‘D’))+364) AND {today}-(to_number(to_char({today}, ‘D’))+357) THEN {amount} ELSE 0 END
- Summary Label: Enter Same Week Last Year
- Select Formula (Currency)
- Summary Type: Sum
- Formula: Enter CASE WHEN {trandate} BETWEEN to_date(TRUNC({today}, ‘MONTH’), ‘MM/DD/YYYY’) AND to_date({today}) THEN {amount} ELSE 0 END
- Summary Label: Enter This Month
- Select Formula (Currency)
- Summary Type: Sum
- Formula: Enter decode(trunc({trandate},’month’),trunc(add_months({today},-12),’month’),{amount},0)
- Summary Label: Enter Same Month Last Year
- Select Formula (Currency)
- Summary Type: Sum
- Formula: Enter CASE WHEN {trandate} BETWEEN to_date(TRUNC({today}, ‘YEAR’), ‘MM/DD/YYYY’) AND to_date({today}) THEN {amount} ELSE 0 END
- Summary Label: Enter This Year to Date
- Select Formula (Currency)
- Summary Type: Sum
- Formula: Enter CASE WHEN {trandate} BETWEEN to_date(TRUNC(TRUNC({today}, ‘YEAR’)-1,’YEAR’), ‘MM/DD/YYYY’) AND to_date(ADD_MONTHS({today},-12)) THEN {amount} ELSE 0 END
- Summary Label: Enter Last Year to Date
7. Click Save