Perform a Count in a formula with a WHERE Clause

[MrHawkins]

Hello,

It's been a while since I've used this software and I'm struggling!!!

I have a report in Crystal 8.5 that performs various groupings and I need to create a formula that can perform a count on a field, but based on a criteria ... how do you do it ???

Cheers Dave.

 

[MColeman]

at the time you want to count

whileprintingrecords;
shared numbervar mycount;
if criteria = condition then
shared numbervar mycount := mycount + 1

at the time you want to print

whileprintingrecords;
shared numbervar mycount;
mycount

after you print
whileprintingrecords;
shared numbervar mycount := 0

 

[MrHawkins]

erm .. I'm lost !!

Looks like your putting some sort of code in, I'm simply trying to put a formula in a field.

Sounds rather simple, but I need to be able to count the number of accurances of X for hourly intervals. So I want to count X where TIME >= 9 (For 9am) and TIME <=10

 

[Katy44]

You might want to put a formula field on your report beside the time field (or where it would go) that displays 1 if criteria are met, zero else. You can then do a count of this field. I had a similar prob, and this worked.

 

[rhinok]

Or you could simply create a Running Total Field (Insert|Field Object)that Counts X where Time In 9 To 10...

Since you have stated where the count field occurs in relation to your groups, its tough to give you a more definite example.


~Kurt

 

[Madawc]

There are several ways: shared variables, summary totals and running totals.

Right-click on a field and choose Insert to get a choice.
Running totals allow you to do clever things with grouping and formulas. The disadvantage is that you cannot test for their values until after the details have been printed.
Summary totals are cruder, but are based directly on the data. Which means that you can use them with a group and suppress a group if it has less than three members, say.

To get yourself familiar with the idea, try doing a test report with a summary total and a running total for the same field, placed on the detail line. You'll find that the running total increases as each line is printed, whereas the summary total has the final value all along.

As for shared variables, I don't use them much, except to pass data from a subreport back to the main report. You can also use variables to show page totals. For normal counting I find running totals or summary totals much easier.


Madawc Williams
East Anglia, Great Britain

 

[MrHawkins]

Cheers for all the help - looks like the running total was the daddy I needed. Was approaching the problem from completely the wrong angle ...

Thanks again

 

[lbass]

If you don't have duplicate data, try a formula like:

if {table.timefield} in time(09,00,00) to time(10,00,00) then 1 else 0

Then you would jright click on the formula and insert a summary (SUM, not count) on this formula.

There are a couple of advantages of conditional formulas over running totals. One is speed--running totals can slow a report, although sometimes running totals are the only solution. Another is that you can position your results in a group header, while running totals need to be place in footers.

-LB

 

[CrystalLisa]

I need to perform a count on some number columns where the value > 0 how do I achieve this?

 

[lbass]

Try:

if {table.number} > 0 then 1 else 0

Then you would right click on the formula and insert a summary (SUM, not count) on this formula.

-LB