Crystal report question - running total?  Variable?

nkuo
nkuo used Ask the Experts™
on
Crystal Report 8.5.  I need a report that shows sales data based on customer join date and purchase activity.  Let's say there are 2 tables one containing contact info and the other purchase history joined by contact_id.  I need to show the count of contacts who made purchases within the first year of them becoming a customer, second year, third year etc.  I tried using a running total using and evaluate using a formula that with a datediff calculation and the numbers does not look right.  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Are you looking for the first purchase or do you need all purchases accounted for?
By that i mean if i signed up in July 2004.  Purchases in Oct 2004, Aug 2005, and Dec 2006  
Do you count
   1st year - 1  
   2nd year - 1
   3rd year -1

mlmcc
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Can you show how you are counting and the results along with sample data?

mlmcc

Author

Commented:
only first purchase.  
Sample data would be something like below
contact table
contact_id      Join_date
1      1/1/2004
2      12/23/2005
3      2/29/2007
4      4/2/2006

Order table
order_id      order_date      order_contact_id
1      6/23/2006      1
2      1/21/2007      1
3      12/1/2006      2
4      2/29/2007      3
5      4/5/2006      4

I created an 'purchased within one year'  distinct count running total evaluating
DateDiff ("d", {Contact.Join_Date},Minimum ({Order.Order_Date}, {Contact.Contact_Id}) )  < 365  
would this work?  What is the best way to create the report I want?


Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Author

Commented:
report can look something like

      Less than 1year      2 years      after_2year
Total      2                           1       1            
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
Try this idea

In the report header add a formula
Add a fromula
Name - DeclVars
WhilePrintingRecords;
Global NumberVar Less1Year := 0;
Global NumberVar TwoYear := 0;
Global NumberVar After2Year := 0;

Add a group to the report - group on Contact_ID
Sort the report on SALE DATE

Add a formula to the group header
Name - WaitTime
WhilePrintingRecords;
Global NumberVar Less1Year;
Global NumberVar TwoYear;
Global NumberVar After2Year;
Local NumberVar WaitTime;

WaitTime := Datediff("yyyy",{qry_contact_orders.Join_Date},{qry_contact_orders.order_date});

if WaitTime = 0 then
   Less1Year := Less1Year + 1
else if WaitTime = 1 then
    TwoYear := TwoYear + 1
else
    After2Year := After2Year + 1;
""

Add formulas to the report footer
Name - Disp1Year
WhilePrintingRecords;
Global NumberVar Less1Year;
Less1Year

Name - Disp2Year
WhilePrintingRecords;
Global NumberVar TwoYear;
TwoYear

Name - DispAfter2Year
WhilePrintingRecords;
Global NumberVar After2Year;
After2Year

You can add text or labels as necessary

mlmcc
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Glad i could help

mlmcc

Author

Commented:
Mlmcc. Thank you very much for your help.  

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial