Record/Group Selection

[jen9814]

I have a data set that contains multiple records for customers; however I only need to do keep the most current to properly count. Below is an example:

customer id log id action date
1234 123 n 1/1/2005
1234 345 n 1/2/2005
1234 456 y 1/3/2005

I would only want to keep the record on 1/3/2004 so when I do counts on actions I will only get 1 'y' and NOT 1'y' and 2 'n'. Does this make sense?

 

[synapsevampire]

Please post your version of Crystal.

One means to limit what's displayed is to use Report->Edit Selection Formula->Group and place something like:

{table.date} = maximum({table.date},{table.cust})

This assumes that you've grouped by the customer.

If you want the count of the maximums this might prove trickier, especially if you want a count of 2 if there are 2 y actions on the same day.

Is this a total count of customers, or the total count of rows per customer for the latest transaction date?

You might use a Running Total as well.

-k

 

[jen9814]

I have version 9.

As for the counting, I am just looking for the total counts for the action. I am trying to eliminate all the previous records for the customers and just take the last one to avoid inflating or incorrect counts.

 

[synapsevampire]

Can the action occur twice on the same day?

Anyway, this might suit you:

Group by Customer, Sort on date descending.

Report header:

whileprintingrecords;
Numbervar MyYCount:=0;
Numbervar MyNCount:=0;
Numbervar MyUnknownCount:=0;

Group Header
whileprintingrecords;
Numbervar MyYCount;
Numbervar MyNCount;
Numbervar MyUnknownCount;
If {table.action} = "y" then
MyYCount:=MyYCount+1
else
If {table.action} = "n" then
MynCount:=MynCount+1
else
MyUnknownCount:=MyUnknownCount+1

Now in the report footer you can display each using:

whileprintingrecords;
Numbervar MyYCount;

changing the variable to suit your needs, or you can use them in formulas.

-k

 

[lbass]

Or you could insert the group selection as suggested by SV:

{table.date} = maximum({table.date},{table.customerID})

And then use the running total expert to create a running total where you count a recurring field, e.g., {table.ID}, evaluate using a formula:

{table.action} = "Y" //Change to "N" for your second running total

Reset never.

-LB

 

[jen9814]

Your suggestions worked like a charm. Thanks for your help.