| Author |
Message |
roxaneamanda
Newbie
Joined: 10 Aug 2009
Location: United Kingdom
Online Status: Offline
Posts: 23
|
  
Topic: Count if
Posted: 11 Aug 2009 at 7:14am |
Is there a way to count if in Crystal.
I have a formula field which acertains if a date is yesterday, it puts Yesterday in a field.
All I want to do is out of the 700 detail lines I have, count how may times the word Yesterday appears in my new column, however, everytime I use count on the field, the formula count {yesterday field} I get 700 results? there are only 157 lines which have yesterday in them?
|
 IP Logged |
|
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 11 Aug 2009 at 7:47am |
You'll need an additional formula that will look something like this:
Then do a sum of this new formula.
Or, there there is a possiblity of duplicates that you only want to count once, you would do it this way:
if ( {@yesterday field}) = 'Yesterday' then {unique key field}
Then set up a Distinct Count of the formula.
-Dell
|
|
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 11 Aug 2009 at 9:07am |
Hilfy,
I have a question on your second option...
if ({@yesterday field}) = 'Yesterday' then {unique key field}
...
I have tried this and will usually get a count that is 1 too many and wonder if I am missing something.
Where the formula=False and the {unique key field} is numeric it will defualt to a Zero, if it is text it is a blank space (not NULL).
Either of these are considered a unique value in a distinctCount even though they are not part of what you want to count making the value one over the desired number.
Can you clarify what I am missing to get the correct value?
Thanks
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 11 Aug 2009 at 9:36am |
Try this:
If ( {@yesterday field}) = 'Yesterday' then {unique key field} else null
Nulls are not considered a unique value - they have no value at all.
-Dell
|
|
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 11 Aug 2009 at 9:42am |
It does not accept NULL as a valid "else" option.
I could never figure out how to get it to use NULL so I gave up on it and use running totals with conditional counts but thought you might have a trick I was missing  |
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 11 Aug 2009 at 9:56am |
Yeah, I haven't ever had to deal with that particular situation because we don't generally default to blank values, we use nulls in the database. I just assumed it would work. 
-Dell
|
|
|
| IP Logged |
|
jcigno
Newbie
Joined: 12 Aug 2009
Location: United States
Online Status: Offline
Posts: 2
|
Posted: 12 Aug 2009 at 10:07am |
|
You missing the point, a UNIQUE field means to count the primary key field in the table. This field must always available. The use the count formula to get your count form the other formula based on the first formula
|
|
Joe
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 Aug 2009 at 10:22am |
Hi J,
Let me see if I can explain. One of the possible issues was that when the tables were joined it created multiple lines all using the same UNIQUE field. E.g. Joining customers to sales.
One can do a DISTINCTCOUNT on that field but Roxanne would have needed a conditional DistinctCount. If you try and use a formula to replicate that process it fails because you cannot make a formula return a NULL in crystal.
The simplest approach for this example (IMO) is to use a conditional Running Total as a DistinctCount on the UNIQUE field with a Evaluate formula as datefield=yesterday and then Reset as Never.
Hope this clarifies issue.
|
| IP Logged |
|
Takesen
Senior Member
Joined: 29 Dec 2008
Location: United States
Online Status: Offline
Posts: 143
|
Posted: 12 Aug 2009 at 4:53pm |
Originally posted by DBlankHilfy,
I have a question on your second option...
if ({@yesterday field}) = 'Yesterday' then {unique key field}
...
I have tried this and will usually get a count that is 1 too many and wonder if I am missing something.
Where the formula=False and the {unique key field} is numeric it will defualt to a Zero, if it is text it is a blank space (not NULL).
Either of these are considered a unique value in a distinctCount even though they are not part of what you want to count making the value one over the desired number.
Can you clarify what I am missing to get the correct value?
Thanks
I've had this problem before where it seems i get 1 over the actual correct total. it always seems to happen when I put the field into the footer...but if you run it in the details it works perfectly.
What i usually end up doing is making another formula to grab that last value and put that in the footer so i can suppress my details.
Does that make sense? it's like it's adding another value in the footer. *shrugs* at least this has been my observation...
But i don't see why they couldn't just use a running total and evaluate on a formula;
and it should count up all the "Yesterday" values. Edited by Takesen - 12 Aug 2009 at 4:54pm
|
|
If IRL is an MMO... I guess we're still in beta...
|
| IP Logged |
|
|
|
