[eleran]

I cannot seem to figure how to derive YTD sales by customer using a saved search. I want to generate a customer list for each of our sales territories showing YTD sales and I just can’t figure it out.

Any help is appreciated.
This is a cached copy. Click here to see the original post.

[rnedelkow]

You could customize this search to show ytd sales only. Currently , it shows last year and this year:

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

2. Choose Customer.

3. Search Title field: entered your preferred name for the search (eg. Sales Last Year and Sales Year to Date)

4. Under Criteria tab > Standard sub tab, on Filter column, display the dropdown field and set the following. Take note that on the popup window that will appear, you will have to choose a description, click Set and click the Add button after each criteria to proceed to the next line.

*Filter *Description

● Transaction: Type is any of (your own set of transaction types)

● Transaction: Main Line is true.

5. Under Results tab > Columns sub tab > Field column, click to display a dropdown and:

● Name with Summary Type = Group.

● Formula (Currency) with Summary Type = Sum and Custom Label = Summary Label = Sales Last Year:

CASE WHEN TO_CHAR({transaction.trandate},’YYYY’)=TO_CHAR({to day},’YYYY’)-1 THEN {transaction.amount} ELSE 0 END

● Formula (Currency) with Summary Type = Sum and Custom Label = Summary Label = Sales YTD:

CASE WHEN TO_CHAR({transaction.trandate},’YYYY’)=TO_CHAR({to day},’YYYY’) AND {transaction.trandate}<={today} THEN {transaction.amount} ELSE 0 END

6. Save and Run.

Thanks,

[eleran]

RE: saved search for YTD sales

Originally posted by rnedelkow

View Post

You could customize this search to show ytd sales only. Currently , it shows last year and this year:

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

2. Choose Customer.

3. Search Title field: entered your preferred name for the search (eg. Sales Last Year and Sales Year to Date)

4. Under Criteria tab > Standard sub tab, on Filter column, display the dropdown field and set the following. Take note that on the popup window that will appear, you will have to choose a description, click Set and click the Add button after each criteria to proceed to the next line.

*Filter *Description

● Transaction: Type is any of (your own set of transaction types)

● Transaction: Main Line is true.

5. Under Results tab > Columns sub tab > Field column, click to display a dropdown and:

● Name with Summary Type = Group.

● Formula (Currency) with Summary Type = Sum and Custom Label = Summary Label = Sales Last Year:

CASE WHEN TO_CHAR({transaction.trandate},’YYYY’)=TO_CHAR({to day},’YYYY’)-1 THEN {transaction.amount} ELSE 0 END

● Formula (Currency) with Summary Type = Sum and Custom Label = Summary Label = Sales YTD:

CASE WHEN TO_CHAR({transaction.trandate},’YYYY’)=TO_CHAR({to day},’YYYY’) AND {transaction.trandate}<={today} THEN {transaction.amount} ELSE 0 END

6. Save and Run.

Thanks,

Thanks. You is a lifesaver.

Is there a way to make this search also show those customers, prospects & leads that do not have transactions yet?

[jon@dogisgood.com]

RE: saved search for YTD sales

I’m getting a ERROR: FIELD NOT FOUND in each column. What needs to be put into the formula field? for ‘YYYY’ I put in ‘2012’ or ‘2013’ . Does something go in to the “to day” brackets?

[rnedelkow]

Hi Jon, the formula should run as is, without setting ‘YYYY’ values. {today} simply pulls today’s date.

Thanks,

[jon@dogisgood.com]

Excellent – got it!

[eleran]

RE: saved search for YTD sales

Also Jon, make sure to remove the gap in the one instance of {to day} that might be the reason for your error

[Kevin MortonMember @kevin_morton]

Does anyone have a working copy of this solution as of today’s NetSuite version?

• This reply was modified 5 years, 8 months ago by Kevin Morton.

[Author]

Posts