Friday, December 21, 2018

Trend Graph to show New Customers (Sales Orders) Monthly filtering specific item

Standard New Customer Sales Order is available as trend graph.  However, reports customized does not appear as an option for custom KPI.

In order to be able to create New customer Sales Order wherein only the first sales order of the customer will be considered on the search and be able to filter it by specific item.


Customer A created in May 2012
First Sales Order created in Sep 2012

The search will count Customer A only in Sep 2012 as this is the first sales order created for this account.  If there will be sales order that will be created after Sep 2012 then Customer A will no longer be included in the next months search.

1.  Go to Transactions>Management>Saved Searches>New
2.  Click on Transactions Link
3.  Enter the name of the search
4.  On Criteria tab>Standard subtab

- Type is Sales Order
- Formula (Numeric) > on the pop up window
-- Formula = case when {customermain.firstorderdate}={trandate} then 1  else 0 end
-- Formula Numeric = equal to
-- Value = 1
- Status is none of sales order:pending approval, sales order cancelled
- Select the item that will be included in the filter of the search

5.  On the Results tab>Columns subtab

Customer (Main Line):Name -- Summary Type = Count
Customer (Main Line):Name -- Summary Type = Group

6.  On Available Filters>set Date then Show in Footer is True
7.  Save the Search
8.  Go to Home Dashboard>click on Personalize Dashboard link
9.  Expand Trend Graphs folder>Click on Custom KPI#1
10.On the portlet > on the right side dropdown arrow > select Set Up

On the pop-up window:

Default Trend Type - Look for the custom search created from above
Select Default Chart Type
Select Trend Type
Show Moving Average = True
Set Period to calculate moving average
Show last Data Point = Yes
Select Chart Theme
Select Custom Series Color
Select Background Type

11.Click Save


No comments:

Post a Comment