| Author |
Message |
iami21
Newbie
Joined: 20 Jul 2009
Online Status: Offline
Posts: 11
|
  
Topic: Count based on a value of a calculated field
Posted: 30 Jul 2009 at 11:47pm |
|
hello!
i have a formula which computes for the datediff in hours. i want to count how many were less than 1hr, between 1-2hrs, etc. if i input the formula as
count ({@datediff}) < 1 it gives boolean
if i use running totals it won't return the correct value. i think i am missing something. pls. enlighten. thank you!
|
 IP Logged |
|
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 31 Jul 2009 at 6:33am |
DBlank is the master of running totals...me, well they confuse me. I use shared variables for all my 'running totals'.
usually 3 formulas to access your shared variables 1) to reset to 0, usually in a group header. 2) to increment the values, usually in details. 3) to display the values, usually in a group footer.
1) is easy: shared numbervar count1:=0;
3) is easy: shared numbervar count1
2) is where the heavy lifting is...not to bad for you:
shared numbervar count1;
local numbervar diff := datediff(h,{table.field1}, {table.field2});
if diff < 1 then
count1 := count1 + 1;
you can expand for the rest of your logic. all of the resetting can be in one formula, but the display has to be in separate formulas (obviously).
= is for comparison, := for assignment. I put "" as the last line of reset and assignment formulas so that nothing displays on the report.
HTH
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 31 Jul 2009 at 7:51am |
I will give you the Running Total version just in case 
This example will assume:
1. you do not have any groups and you want the totals for the entire report.
2. Your @datediff formula is returning the correct value
Also Running Totals do not work on headers. You must use them in footers (or incrimental changes on detail row).
Add a New RT named "Less than 1 hour" (or whatever)
Field to summarize = primary key field (although you can use the @datediff if you want)
Type of summary = COUNT
Evaluate toggle the "Use a formula" and then click on the formula button and add you condition there.... {@datediff} < 1Reset = Never
Place in your Report footer
For the next one do the same thing but chaneg the name... "1 to 2 hours"
and change your Evaluate formula to {@datediff} in 1 to 2
Repeat for each count type you want
|
| IP Logged |
|
iami21
Newbie
Joined: 20 Jul 2009
Online Status: Offline
Posts: 11
|
Posted: 02 Aug 2009 at 8:52pm |
|
Thanks lockwell (you remembered my previous posts hehe) and DBlank!I will try this out and give you feedback. Thanks for all the help this is a great site!
|
| IP Logged |
|
NedSchneebly
Newbie
Joined: 09 Jul 2009
Location: Canada
Online Status: Offline
Posts: 23
|
Posted: 13 Aug 2009 at 1:17pm |
Hi all - I am using CR XI and have another syntax question. I am also trying to write a formula based on running totals, but am using lockwelle's method instead of DBlanks (ie I am not using CR's Running Totals, but rather writing 3 formulas and placing them in the header, details and footer). Using this example code from lockwelle (my code is different):
1) is easy: shared numbervar count1:=0;
3) is easy: shared numbervar count1
2) is where the heavy lifting is...not to bad for you:
shared numbervar count1;
local numbervar diff := datediff(h,{table.field1}, {table.field2});
if diff < 1 then
count1 := count1 + 1;
I am having a problem with variable declaration. I declare a variable in section 1 like so:
Global NumberVar count1 := 0;
and that works. It displays the results of this formula as '0' in the header.
In section 2, I do my math:
Global NumberVar count1 = count1 + 1;
and this works as well, displaying 1, 2, 3 etc in the Details section after each record.
But in section 3 (the footer), if I put:
Global NumberVar count1;
it displays it as zero again (I assume that if no assignment is made, CR initializes that count1 variable again. If I make no variable declaration at all and just put:
ToText (count1);
it gives me an error message saying the ")" is missing. Not sure what it is expecting here. Any ideas? (apologies in advance if this is an RTFM-type question, but I've been searching the 'F' M for hours!).
|
|
Sigless and Bible Black
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 13 Aug 2009 at 1:25pm |
i think that the issue is in the assignment...
global numbervar count1 = count1 +1;
should be:
global numbervar count1 := count1 +1;
Crystal shouldn't be initializing it back to 0, otherwise, how would you ever display it. Every now and then I have had issues, but those have been suppression formulas (I was baffled). Otherwise, if you have the display formula in the footer of the same group, all should be fine.
HTH
|
| IP Logged |
|
NedSchneebly
Newbie
Joined: 09 Jul 2009
Location: Canada
Online Status: Offline
Posts: 23
|
Posted: 13 Aug 2009 at 1:37pm |
|
Oops - sorry, that is just a typo (in this forum) on my part. That isn't the formula I'm actually using, but rather an example. And that was in section 2 as well, which is working fine. It is section 3 (the footer) that I am stuck on. If I don't declare the variable, it gives me the "missing ')'" error. If I do declare the variable, it re-initializes it.
It would be nice to not have to declare that variable at all in sections 2 & 3, since I already did so in section 1, and it is "global". Thanks.
|
|
Sigless and Bible Black
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 13 Aug 2009 at 1:52pm |
It is not that the variable needs to be declared again, it just how Crystal accesses. Global is actually less global than shared, but for what you are doing, it doesn't matter.
just to make sure, your report looks something like:
gh1 (count1:=0)
details (count1:=count1+1)
gf1 (count1)
The most common error that I have done is to mistype the name of the variable. I don't know if Crystal is case sensitive, I've read that isn't, but I don't trust it (I think that I tested it), so I always make sure that the variables match in case as well as spelling. Since the declaration is so loose, you can declare a variable, but it has never been used anywhere else, and Crystal won't raise an error...this is what I have done several times, or I changed the name of the variable in one formula and not the others...silly me.
Just some things to check. Assignment vs equality also get me every now and then. Crystal doesn't reinitialize...ever, which is why you have to tell it in the group header that the value := 0.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 13 Aug 2009 at 2:35pm |
Not that variables are my thing at all but did you use the whileprintingrecords in your GF1 formula statement?
Now that I have stepped way out of my comfort zone ... just use a RT. It only takes one field creation (with point and click so no typos) and you can use it to display on both the detail and group footer (without it adding the extra count in the footer!). 
|
| IP Logged |
|
NedSchneebly
Newbie
Joined: 09 Jul 2009
Location: Canada
Online Status: Offline
Posts: 23
|
Posted: 13 Aug 2009 at 2:42pm |
|
Nope. Definitely not a typo, not an assignment problem, nothing to do with case-sensitivity.
Simple put, I just cannot figure out the syntax for formula 3 (footer).
Here is what I have tried, and the error each produces:
Global NumberVar count1;
ToText (count1, 0);
no error, but displays 0
Global NumberVar count1 := count1;
ToText (count1, 0);
no error, but displays 0
count1;
error: the remaining text does not appear to be part of the formula
ToText (count1, 0);
error: The ) is missing
(ToText (count1, 0));
error: The ) is missing
I'm pretty much out of guesses at this point. I have proved that count1 is greater than 0, by displaying it in formula 2 (details). So why can't I properly access and display that variable in formula 3?
|
|
Sigless and Bible Black
|
| IP Logged |
|
|
|
