Friday, November 30, 2018

Report on the Percentage Ratio of Return Authorizations over Sales per Customer

1. Navigate to Reports > Saved Searches > All Saved Searches > New.

2. Click Transaction.

3. Search Title field: entered your preferred name for the search (eg. Rate of Return)

4. Under Criteria tab > Standard sub tab, on Filter column, display the dropdown field and set the following.

Type is any of Return Authorization, Cash Sale, Invoice.
Main Line is true.

5. Under Results tab > Columns sub tab > Field column set the following:

● On Name field set Summary Type Group.
● Add Formula (Currency) then set Summary Type to Sum then add Summary Label like Sales
   CASE WHEN {type} IN ('Invoice','Cash Sale') THEN {totalamount} ELSE 0 END
● Add Formula (Currency) then set Summary Type to Sum, set the Function to Absolute Value, then add Summary Label like Returns
  CASE WHEN {type}= 'Return Authorization' THEN {totalamount} ELSE 0 END
● Amount (Transaction Total) then set Summary Type to Sum then Add Summary Label like Sales - Returns
● Add Formula (Percent) then set Summary Type to Maximum, set the Function to Round to Hundredths, then add Summary Label like Rate of Return

  ABS(SUM(CASE WHEN {type}= 'Return Authorization' THEN {totalamount} ELSE 0 END))/NULLIF(SUM(CASE WHEN {type} IN ('Invoice','Cash Sale') THEN {totalamount} ELSE 0 END),0)

6. Click Save & Run.

Note: If you want to run this search for particular customers, just add Name filter then choose the customer names.



No comments:

Post a Comment