Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Help counting records that meet criteria Post Reply Post New Topic
Author Message
zann
Newbie
Newbie


Joined: 29 Sep 2010
Online Status: Offline
Posts: 2
Quote zann Replybullet Topic: Help counting records that meet criteria
    Posted: 29 Sep 2010 at 8:52am
Hi everyone,

I'm new to writing formulas in Crystal.  I have version XI.

What I'm trying to do is count the number of records in a field that fall within a range, then return that result. 

A simplified example of what I want to end up with:

Grouped data (Orders by Company name)
0-100 pounds       5
101-200 pounds    6
201-300 pounds    1

I've experimented with the Count formula and control structures, but I don't see the solution.  I noticed there are Range functions but don't understand how to make them work here.  Can anyone please help me put it together?


Edited by zann - 29 Sep 2010 at 8:52am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 Sep 2010 at 10:03am
please explain your row level data that you want to get into the format you posted
IP IP Logged
zann
Newbie
Newbie


Joined: 29 Sep 2010
Online Status: Offline
Posts: 2
Quote zann Replybullet Posted: 29 Sep 2010 at 10:12am
Just found one solution, however messy it is.   In case someone needs it, first create formula fields with this:

if ({table.field} = "criteria")
then 1
else 0

You can then create a second formula to sum this field to get a count of records matching the criteria and place this field on the report.  This is working for me so far.

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 Sep 2010 at 10:17am
That is one solution
or you can use Running totals
or you can use shared variable formulas
or you can create a formula to make your 'groups' (e.g. 0-100 lbs) and then do sums/counts on that grouping
but it is hard to tell without knowing what your data looks like
IP IP Logged
rvink
Groupie
Groupie
Avatar

Joined: 04 Feb 2008
Location: New Zealand
Online Status: Offline
Posts: 55
Quote rvink Replybullet Posted: 29 Sep 2010 at 10:18am
Can you filter the record set using the Select Expert so your data only contains the fields that fall in the range you want?

If your report needs to select a wider range of data and you only want to count a subset, could you group the data and count the data in that group?

Have you looked at DistinctCount vs Count?

You could create a formula like this:

if {table.field} >= lowerlimit and {table.field} <= upperlimit then
   1
else
   0

You can then sum the formula to count the records you want.

IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.