Include in Count if Certain Criteria is Met

[borisbe]

I am using Crystal 8 with a csv file. I hope I can word this problem so it makes sense.

CSV File:

ID Task

1 95555
1 55555
1 52154
1 62154


2 95555
2 55587

3 84444
3 55522

4 84444
4 95212
4 51555

5 95555
5 54545

Needed Results:

Task 95555 Other Tasks

3 5

Three IDs have task 95555 and the total of the other tasks for these IDs are 5

***

Task 84444 Other Tasks

2 3

Two IDs have task 84444 and the total of the other tasks for these IDs are 3

Help would be appreciated.

Thank you

 

[Madawc]

You want the total of tasks that are NOT 95555, when at least one task in the group is 95555, right? And likewise for 84444?

For 95555, add the same dataset as an alias, but just for 95555s. Link to the orignal dataset via the ID. This will eliminate every group that doesn't have at least one 95555. You can then count those that are not 95555.

For 84444, you could put a sub-report in the group header or footer, using the same method. If you saved the first report under another name you could in fact re-import it as a subreport and then change details.

All of this would work in 8.5, the first version I used. I understand that 8.0 was similar.

You might also look at crosstabs - I don't think they'll do what you want but they do something similar.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[hilfy]

Instead of using subreports, I usually create formulas like this:

@Is95555
If {table.task} = 95555 then 1 else 0

@Not9555
If {table.task} <> 95555 then 1 else 0

You then include a Sum of these formulas to get your counts.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[lbass]

Try something like this. Create a formula {@null} where you create a new formula and save it with nothing in it. Then set up formulas like:

if {table.task} = 95555 then {table.ID} else tonumber({@null}) //assuming ID is a number field

if {table.task} <> 95555 then {table.ID} else tonumber({@null})

Then you can insert distinctcounts on these formulas to get the number of IDs that meet your criteria. hilfy's solution would also work, if you only have one instance of each task per ID.

-LB

 

[ianoctdec]

LBass,
I am trully amazed of how creative you can be, and want to thank you for sharing these wonderfull ideas!
I had to do something like this, but I didn't think that an empty formula would work.
Instead I created a formula as:
@ValidCO
if table.OrderDate = {?DateRange} then
table.OrderDate
else
''
Then, I used a running total evaluated on a formula to make a distinct count of @ValidCO.
I like your solution better.

Dana