Friday, May 31, 2019

Create a Transaction Saved Search which Displays Separate Columns for New and Existing Customer Sales



While the New Customer Sales report displays transactions for new customers, it does not have the ability to show a separate column to display the sales for existing customers. For this, users can create a search instead. See the steps below:

1. Navigate to Lists > Search > Saved Searches > New > Transaction.
2. Under the Criteria tab > Standard sub tab, add the following:

Filter
Description
Formula
Typeis any of Invoice, Cash Sale 
Main Lineis true 
Dateis within    <---- select date range here
Amountis greater than 0.00 

3. Under the Results tab > Columns sub tab, add the following:

 
Field
Summary Type
Function
Formula
Custom Label



 
Formula (Text)  case when {trandate}={customer.firstsaledate} THEN 'New Customer' ELSE 'Returning Customer' END New/Returning   
 
Date        
 
Type        
 
Number    Transaction #   
 
Name        
 
Formula (Numeric)  case when {trandate}={customer.firstsaledate} THEN 1 ELSE Null END Count of New Sale   
 
Formula (Numeric)  case when {trandate}!={customer.firstsaledate} THEN 1 ELSE Null END Count of Returning Customer   
 
Formula (Currency)  case when {trandate}={customer.firstsaledate} THEN {Amount} ELSE Null END New Customer Sales   
 
Formula (Currency)  case when {trandate}!={customer.firstsaledate} THEN {Amount} ELSE Null END Returning Customer Sales   
 
* Please mark 'Show Totals' box, so it will sum the total the amount and count of transactions. 

4. Give it a name and save.

No comments:

Post a Comment