nkuo
asked on
Crystal report question - running total? Variable?
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.
Can you show how you are counting and the results along with sample data?
mlmcc
mlmcc
ASKER
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},Minimu
would this work? What is the best way to create the report I want?
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},Minimu
would this work? What is the best way to create the report I want?
ASKER
report can look something like
Less than 1year 2 years after_2year
Total 2 1 1
Less than 1year 2 years after_2year
Total 2 1 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad i could help
mlmcc
mlmcc
ASKER
Mlmcc. Thank you very much for your help.
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