Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: How to identify new customer for a quarter? Post Reply Post New Topic
Page  of 2 Next >>
Author Message
smuru1975
Newbie
Newbie


Joined: 17 Sep 2010
Online Status: Offline
Posts: 5
Quote smuru1975 Replybullet Topic: How to identify new customer for a quarter?
     Posted: 17 Sep 2010 at 2:43am
Hello Experts,
 
There is a requirement to develop a report (ex: New customer report) and this report has two parameter Quarter and Year.

Depends upon the quarter and year, system should extract the details and check against for the past one year sales for products. If you are able to find the sales of the products in the past one year then we should exclude those records from the output and if you don’t find the sales in the past then it should display the records as an output of the report.

Can any one help me, how to write a condition in the report selection creteria? Your help  on this would be highly appritiated.

 
Regards
Murugan
Murugan
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 17 Sep 2010 at 3:54am
1. what is your definition of the past year? the quarter seelcted and the previous 3 quarters from that?
2. do you mean if you find that you only want to display customers if they boght something in the quarter seelcted by the parameters and did not purchase something in the previous 3 quarters?
IP IP Logged
swigartd
Newbie
Newbie
Avatar

Joined: 16 Sep 2010
Location: United States
Online Status: Offline
Posts: 13
Quote swigartd Replybullet Posted: 17 Sep 2010 at 4:15am
I would check the minimum date of the transactions and exclude all detail lines that preceded the start date of the quarter.
 
Dave
Aiming beyond mediocrity
IP IP Logged
smuru1975
Newbie
Newbie


Joined: 17 Sep 2010
Online Status: Offline
Posts: 5
Quote smuru1975 Replybullet Posted: 18 Sep 2010 at 5:13pm
Hi,
 
Thank you asking the clarificaitons.
 
1.  Let say, today is 19-Sept-2010 and we are at 3rd quarter of year 2010. If I run the report, it should select the records of actual sales happened in the quarter 3 (ie, july, aug and sept), but did not purchase something from 01-July-2009 to 31-Jun-2010 (ie, for the past one year).
 
If the above condition matches, then I would say these customers are new customer of this quarter and there were no sales for these idenifier customers for the past one year.
 
2. Yes, you are right.
 
Kindly give me some lights on this...your help is much appritiated.
 
Regards
Muru
Murugan
IP IP Logged
smuru1975
Newbie
Newbie


Joined: 17 Sep 2010
Online Status: Offline
Posts: 5
Quote smuru1975 Replybullet Posted: 19 Sep 2010 at 3:22pm
Thanks Dave for the input. Would it be possible to write up bit deatail on this?  For ex: I have a field name called 'Sales_date', 'Acutal_qty', 'Quarter', 'Year'...how are you suggesting on the record selection creteria.
Murugan
IP IP Logged
swigartd
Newbie
Newbie
Avatar

Joined: 16 Sep 2010
Location: United States
Online Status: Offline
Posts: 13
Quote swigartd Replybullet Posted: 20 Sep 2010 at 3:34am
I would put the detail lines with the dates in a group by customer.
 
In the group footer, put in a "summary" field which gives the MINIMUM date for the transactions.
 
Hide the group detail and group headers.  If the minimum date is less than your report month, hide the group footer line as well.  The only lines that are visable will be those that fit your criterium.
 
Good luck.
 
Dave
 
Aiming beyond mediocrity
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Sep 2010 at 3:52am

you still need to limit your data to 1 year of data based on the params

I think this will do that:
{table.Sales_date} in
dateserial({?year}-1,(if {?quarter}=1 then 1 else if {?quarter}=2 then 4 else if {?quarter}=3 then 7 else if {?quarter}=4 then 10),1)
to
dateserial({?year},(if {?quarter}=1 then 1 else if {?quarter}=2 then 4 else if {?quarter}=3 then 7 else if {?quarter}=4 then 10)+3,1-1)
 
from there Daves process works although you still need to know either the beginning of the quarter or you can use the datepart of the quarter from the minimum date of the customer for suppression
IP IP Logged
smuru1975
Newbie
Newbie


Joined: 17 Sep 2010
Online Status: Offline
Posts: 5
Quote smuru1975 Replybullet Posted: 26 Sep 2010 at 8:55pm

Hi,

Thanks for your help and advise and it is working perfectly on the record selection criteria...ie, below is the code in my report selection

((If {?SalesChannel}<>'ALL'
Then {GZ_QUA_FCST_COMP_REP.LEVEL9} IN {?SalesChannel}
Else True)
and
(If {?SalesPerson}=''
Then {GZ_QUA_FCST_COMP_REP.LEVEL8} like '*'
Else {GZ_QUA_FCST_COMP_REP.LEVEL8} = {?SalesPerson})
and
(If {?ProdLine}<>'ALL'
Then {GZ_QUA_FCST_COMP_REP.LEVEL6} IN {?ProdLine}
Else True)
and
(If {?Year} = ''
Then {GZ_QUA_FCST_COMP_REP.SALES_YEAR}  like '*'
Else {GZ_QUA_FCST_COMP_REP.SDATE} in
dateserial(ToNumber({?year})-1,(if {?quarter}='1' then 1 else if {?quarter}='2' then 1 else if {?quarter}='3' then 4 else if {?quarter}='4' then 7),1)
to
dateserial(ToNumber({?year}),(if {?quarter}='1' then 1 else if {?quarter}='2' then 4 else if {?quarter}='3' then 7 else if {?quarter}='4' then 10)+3,1-1)));

However when I want to find the new customer based on these records, below are the steps, I have been trying for the past couple of days and I am unable to get the required output.
 
1. Grouping is based on Customer Name + Product Code + Part Id, hence I have created a group formula based on these three columns.
 
2. As per Dave suggesion,  in the Group footer  suppress(No Drill down), have written the following formula to identify the new customer based on few checking.
 
if ({GZ_QUA_FCST_COMP_REP.LEVEL1} <> '' AND {GZ_QUA_FCST_COMP_REP.LEVEL7} <> '') AND (Minimum ({GZ_QUA_FCST_COMP_REP.SDATE}, {@Groupformula}) in dateserial(ToNumber({?year}),(if {?quarter}='1' then 1 else if {?quarter}='2' then 4 else if {?quarter}='3' then 7 else if {?quarter}='4' then
10)+3,1-1) AND NOT (if {GZ_QUA_FCST_COMP_REP.SDATE} in
dateserial(ToNumber({?year})-1,(if {?quarter}='1' then 1 else if {?quarter}='2' then 1 else if {?quarter}='3' then 4 else if {?quarter}='4' then 7),1)
to
dateserial(ToNumber({?year}),(if {?quarter}='1' then 1 else if {?quarter}='2' then 1 else if {?quarter}='3' then 4 else if {?quarter}='4' then 7)+3,1-1)then true else false)) then
True
else
false
 
 
But I am not able to get the expected output, always I am getting the total number of records retrieved from the selection creteia.
 
Either you or Dave could suggest or help me on this would be greatly appritiated.
 
Regards,
Murugan
IP IP Logged
swigartd
Newbie
Newbie
Avatar

Joined: 16 Sep 2010
Location: United States
Online Status: Offline
Posts: 13
Quote swigartd Replybullet Posted: 27 Sep 2010 at 2:05am
Murugan,
I'm glad your are getting to your answer.
 
My understanding was that you only wanted to know who the new clients were during the period.  That is why I suggested to get the minimum date for the detail records.  If the minimum date was before your required date then the client isn't new. 
 
What other data do you want to see?
Aiming beyond mediocrity
IP IP Logged
smuru1975
Newbie
Newbie


Joined: 17 Sep 2010
Online Status: Offline
Posts: 5
Quote smuru1975 Replybullet Posted: 27 Sep 2010 at 3:56am

Thanks for your quick reply and asking for the details. Let me try to make an attempt of requirement.

1.  The product is being sold to different locations(customer) on differnt date.

2. The report should have the following outputs
----------------------------------------------------------------------------------------------
Customer no Cust Name Product code Part Id Units_sold Revenue  Margin
---------------------------------------------------------------------------------------------
3. User will pass on the below parameters while running the report
 
Currency, Sales_channel, Sales_person, Product Line, Year & Quarter
 
4. Based on the year & Quarter, system should identify new customer details.
 
5. What is the logic need to be used to identify new customer?
 
For ex: Year = 2010 and Quarter = 3
In this case I need to identify all the new customers from 01-Jul-2010 to 31-Sept-2010 by checking these three months data against previous years sales data (ie, from 01-Jul-2009 to 31-Jun-2010) . If I don't find the record existence in the previous year, then those records are considered as new sales for the quarter 3 and year 2010.
 
Where am I stuck up currently?
 
1. Through record selection, I am able to retrieve 15 months of records (ie, considering the above example from 01-Jul-2009 to 31-Sept-2010).
2. I have designed the report format and wrote the formula for each fields.
3. Not sure what extractly need to do on report footer section to pull out new customer records for the given quarter....
 
If you could advise on this(point 3) would be greately helpful as I have been trying this for almost a week of time.
 
Regards,
 
Murugan
IP IP Logged
Page  of 2 Next >>
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.