GeorgeSalet
asked on
Running Total by group 2
Well I guess I jumped the gun without checking my results
I am trying to count the number of groups for a condition, if one of the records in the group is true the group will be true. I am using this formula then summing the formula with a running total. when there are more than 1 record and the last record is equal to 0 the formula result is 0 instead of 1
If({@TimeWork}+{@Travel})> 0 Then 1
group
Record-1 true
Record-0 false
returns value 0 Need value of 1
Thanks
George
I am trying to count the number of groups for a condition, if one of the records in the group is true the group will be true. I am using this formula then summing the formula with a running total. when there are more than 1 record and the last record is equal to 0 the formula result is 0 instead of 1
If({@TimeWork}+{@Travel})>
group
Record-1 true
Record-0 false
returns value 0 Need value of 1
Thanks
George
ASKER
Mimcc
my problem is that the formula returns result for each record, If one record in the group is true the group should be true. My current expression allows the last record to control the group.
Thanks
george
my problem is that the formula returns result for each record, If one record in the group is true the group should be true. My current expression allows the last record to control the group.
Thanks
george
What are @TimeWork and @Travel?
mlmcc
mlmcc
mlmcc,
In my experience, if you don't include an "else" part in a formula like that, you just get the default value of the same data type as the value in the "then" part (0 in this case). I've used countless formulas like that with no "else" and never had a problem.
George,
If the {@TimeWork} and {@Travel} formulas operate on the record level, then the result from that formula in a group footer will be the result from the last record in the group. You mentioned something about using the formula with a running total, but didn't provide any details.
If you just want to know if that formula produced 1 for any of the records in the group, you could use
Sum ({your formula}, {group field}) > 0
James
In my experience, if you don't include an "else" part in a formula like that, you just get the default value of the same data type as the value in the "then" part (0 in this case). I've used countless formulas like that with no "else" and never had a problem.
George,
If the {@TimeWork} and {@Travel} formulas operate on the record level, then the result from that formula in a group footer will be the result from the last record in the group. You mentioned something about using the formula with a running total, but didn't provide any details.
If you just want to know if that formula produced 1 for any of the records in the group, you could use
Sum ({your formula}, {group field}) > 0
James
Create a new formul called @Null do this by opening a new formula nd closing without entering anything in the formula editor
Change your existing formula to
//@TimeTotal
If({@TimeWork}+{@Travel})> 0 Then
1
Else
ToNumber({@Null})
Then use this in your running total
Field to summarise -> @TimeTotal
Type of Summary -> DistinctCount
Evaluate -> On change of group 1
Reset -> Never
Change your existing formula to
//@TimeTotal
If({@TimeWork}+{@Travel})>
1
Else
ToNumber({@Null})
Then use this in your running total
Field to summarise -> @TimeTotal
Type of Summary -> DistinctCount
Evaluate -> On change of group 1
Reset -> Never
ASKER
I am not sure I explained what I am trying to do very clearly<GRIN>
Timework and travel represent time. I am trying to count the number of groups(Employee Dispatches) that have "positive time" some groups have no time and should be ignored.
each group could have from 1 to 20 records in it, Simply put I want to know how many dispatches(groups) have time in them.
Thanks for all your help
George
Timework and travel represent time. I am trying to count the number of groups(Employee Dispatches) that have "positive time" some groups have no time and should be ignored.
each group could have from 1 to 20 records in it, Simply put I want to know how many dispatches(groups) have time in them.
Thanks for all your help
George
What are the 2 formulas?
When they have no time is that a NULL value or 0?
Where is the formula you are using in the report?
mlmcc
When they have no time is that a NULL value or 0?
Where is the formula you are using in the report?
mlmcc
ASKER
I am using this formula to figure the time for each record:
DATEDIFF ('s',DateTime(cdate({DispT ech.DispDa te}), time({DispTech.TimeOn})), DateTime(cdate({DispTech.D ateOff}),t ime({DispT ech.TimeOf f})))/3600
I then have a running total at the group level R-TotalHrs that sums the records for each group
and travel running group is R-Travel
I tried using R-TotalHrs and R-Travel in the formula but CR Rejected that
George
DATEDIFF ('s',DateTime(cdate({DispT
I then have a running total at the group level R-TotalHrs that sums the records for each group
and travel running group is R-Travel
I tried using R-TotalHrs and R-Travel in the formula but CR Rejected that
George
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Mimcc
I want to make sure I understand this, 4 separate formulas?
1st in the header.. resets memory variable?
2nd in details to populate the variable
3rd in group footer to total count
4th in footer to display results
Do I name them the same? and is that while printing records?
Sorry to be so lame
George
I want to make sure I understand this, 4 separate formulas?
1st in the header.. resets memory variable?
2nd in details to populate the variable
3rd in group footer to total count
4th in footer to display results
Do I name them the same? and is that while printing records?
Sorry to be so lame
George
Yes, 4 formulas. Give each its own name related to what they do.
Yes use the WhilePrintingReccords
mlmcc
Yes use the WhilePrintingReccords
mlmcc
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes it should.
That's what happens when you type slower than you think
mlmcc
That's what happens when you type slower than you think
mlmcc
That's why I try to think slowly. :-)
James
James
ASKER
And My brain is going a mile a minute just trying to keep up with your typing
George
George
:-)
ASKER
I am having some problems with Mimcc: code
To answer James questions 1st
//@Timework
DATEDIFF ('s',DateTime(cdate({DispT ech.DispDa te}), time({DispTech.TimeOn})), DateTime(cdate({DispTech.D ateOff}),t ime({DispT ech.TimeOf f})))/3600
//@travel
DATEDIFF ('s',DateTime(cdate({DispT ech.DispDa te}), time({DispTech.DispTime})) , DateTime(cdate({DispTech.D ispDate}), time({Disp Tech.TimeO n})))/3600
I created 4 formulas and recieved two errors.... not sure where to go from here
//@inv-1
WhilePrintingrecords;
Global NumberVar GroupsWithTime := 0;
""
Placed in header at runtime recieved error "A running total cannot refer to a print time formula"
//@TimeTotal
If({@TimeWork}+{@Travel})> 0 Then
1
Else
0
Placed in detail No errors
//@inv-2
WhilePrintingrecords;
Global NumberVar GroupsWithTime;
If Max({@TimeTotal},{GroupFie ld}) then
GroupsWithTime := GroupsWithTime + 1;
""
Placed in group footer Recieved error: Max was highlighted "a number, currency, time is expected"
//@inv-3
WhilePrintingrecords;
Global NumberVar GroupsWithTime;
GroupsWithTime
Placed in report footer
Thanks
George
To answer James questions 1st
//@Timework
DATEDIFF ('s',DateTime(cdate({DispT
//@travel
DATEDIFF ('s',DateTime(cdate({DispT
I created 4 formulas and recieved two errors.... not sure where to go from here
//@inv-1
WhilePrintingrecords;
Global NumberVar GroupsWithTime := 0;
""
Placed in header at runtime recieved error "A running total cannot refer to a print time formula"
//@TimeTotal
If({@TimeWork}+{@Travel})>
1
Else
0
Placed in detail No errors
//@inv-2
WhilePrintingrecords;
Global NumberVar GroupsWithTime;
If Max({@TimeTotal},{GroupFie
GroupsWithTime := GroupsWithTime + 1;
""
Placed in group footer Recieved error: Max was highlighted "a number, currency, time is expected"
//@inv-3
WhilePrintingrecords;
Global NumberVar GroupsWithTime;
GroupsWithTime
Placed in report footer
Thanks
George
Max should be Maximum
Do you have a running total that uses inv-1?
mlmcc
Do you have a running total that uses inv-1?
mlmcc
ASKER
Mimcc:
I did however since I saved the file the only error is with the Max highlighted. When you said Max should be Maximum did you mean that literally to change it to maximum??
I did however since I saved the file the only error is with the Max highlighted. When you said Max should be Maximum did you mean that literally to change it to maximum??
ASKER
Mimcc:
I changes max to maximum, now I am getting another error
//inv-2
WhilePrintingrecords;
Global NumberVar GroupsWithTime;
If Maximum({@TimeTotal},{Grou pField}) then
GroupsWithTime := GroupsWithTime + 1;
""
Groupfield is highlighted with the following error "field name not known"
should I ad a number variable?
I changes max to maximum, now I am getting another error
//inv-2
WhilePrintingrecords;
Global NumberVar GroupsWithTime;
If Maximum({@TimeTotal},{Grou
GroupsWithTime := GroupsWithTime + 1;
""
Groupfield is highlighted with the following error "field name not known"
should I ad a number variable?
GroupFIeld is the name of you field for the group.
mlmcc
mlmcc
ASKER
Mimcc:
Well were getting close.
I tried inserting the groupname with little sucess, could be I don't know what I am doing<LOL>
here is how I changed it
//inv-2
WhilePrintingrecords;
Global NumberVar GroupsWithTime;
If Maximum({@TimeTotal},Group Name ({Dispatch.Dispatch})) then
GroupsWithTime := GroupsWithTime + 1;
Thanks
George
""
Well were getting close.
I tried inserting the groupname with little sucess, could be I don't know what I am doing<LOL>
here is how I changed it
//inv-2
WhilePrintingrecords;
Global NumberVar GroupsWithTime;
If Maximum({@TimeTotal},Group
GroupsWithTime := GroupsWithTime + 1;
Thanks
George
""
No, Just select the field from the field list in the formula editor
mlmcc
mlmcc
IOW, if your group is on {Dispatch.Dispatch}, that's what you use to replace {GroupField}. Not GroupName ({Dispatch.Dispatch}). Just the field name.
James
James
ASKER
Wow
Thanks for all the help!!!
I re-read and re-read all the info and it finally clicked
I split the credit because Mimcc: formulas would not work without James input.
Thanks Again
George
Thanks for all the help!!!
I re-read and re-read all the info and it finally clicked
I split the credit because Mimcc: formulas would not work without James input.
Thanks Again
George
You're welcome. Glad I could help.
James
James
If({@TimeWork}+{@Travel})>
1
Else
0
Your formula returns NULL for the value and hwen NULL is added to anything the result is NULL
mlmcc