Solved

Crystal report question - running total?  Variable?

Posted on 2007-04-06
7
340 Views
Last Modified: 2010-05-18
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.  
0
Comment
Question by:nkuo
  • 4
  • 3
7 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 18864616
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 18864618
Can you show how you are counting and the results along with sample data?

mlmcc
0
 

Author Comment

by:nkuo
ID: 18865126
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?


0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:nkuo
ID: 18865162
report can look something like

      Less than 1year      2 years      after_2year
Total      2                           1       1            
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 125 total points
ID: 18865598
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 18867237
Glad i could help

mlmcc
0
 

Author Comment

by:nkuo
ID: 18867266
Mlmcc. Thank you very much for your help.  
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Break apart and rearrange field, Crystal Reports 9 74
display n/a in column 2 31
Crystal Reports Licensing Questions 4 34
Count If in Crystal Reports 2 18
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question