Forums › Cached › Customization › Reporting › Report Builder
This topic contains 7 replies, has 2 voices, and was last updated by Kevin Morton 5 years, 1 month ago.
-
AuthorPosts
-
eleran- Contributions: 0
- Level 1
- ☆
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- Contributions: 0
- Level 1
- ☆
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- Contributions: 0
- Level 1
- ☆
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- Contributions: 0
- Level 1
- ☆
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- Contributions: 0
- Level 1
- ☆
Hi Jon, the formula should run as is, without setting ‘YYYY’ values. {today} simply pulls today’s date.
Thanks,
-
jon@dogisgood.com- Contributions: 0
- Level 1
- ☆
Excellent – got it!
-
eleran- Contributions: 0
- Level 1
- ☆
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
-
Does anyone have a working copy of this solution as of today’s NetSuite version?
- This reply was modified 5 years, 1 month ago by Kevin Morton.
-
AuthorPosts
You must be logged in to reply to this topic.