if the serviceid, client and date are the same but the crew name is different, I want to be able to assign a % to the amount of work a
multi person crew does for the service id. The only thing I could accomplish
was to read, count and calculate 2 lines of data. I cannot get it to compare and
calculate more than 2 lines of data.
Here is what the results should look like (the job %
column is the results I am looking for) -
|
client
|
SvcID
|
crew name
|
date
|
Job %
|
|
q
|
123
|
abc
|
10/1/2016
|
33%
|
|
q
|
123
|
def
|
10/1/2016
|
33%
|
|
q
|
123
|
jkl
|
10/1/2016
|
33%
|
|
q
|
456
|
qwe
|
10/1/2016
|
100%
|
|
q
|
321
|
sdf
|
10/1/2016
|
100%
|
|
w
|
753
|
tyu
|
10/1/2016
|
50%
|
|
w
|
753
|
poi
|
10/1/2016
|
50%
|
|
w
|
951
|
qwe
|
10/1/2016
|
100%
|
|
o
|
654
|
abc
|
10/1/2016
|
33%
|
|
o
|
654
|
def
|
10/1/2016
|
33%
|
|
o
|
654
|
jkl
|
10/1/2016
|
33%
|
|
o
|
369
|
fgh
|
10/1/2016
|
100%
|
|
o
|
369
|
fgh
|
10/1/2016
|
100%
|
|
o
|
147
|
fgh
|
10/1/2016
|
100%
|
I have use 'next record' and 'previous record' commands as
well as 'onfirstrecord' and 'onlastrecord' commands. I've also tried loops.
Here is the formula I am currently using.
Shared Numbervar c;// counts the number of serviceids
with different crews c:=1; If ( onlastrecord or {Command.ServiceId}= next({Command.ServiceId})
and {Command.CrewName} = next({Command.CrewName}) and {@Date} =
next({@Date})
//the Date formula is date({Command.OutDateTime})
)
or (
{Command.ServiceId}<> next({Command.ServiceId})
)
Then
(
c:=c+0
)
Else
If (
{Command.ServiceId}=next({Command.ServiceId})
and
{Command.CrewName} <> next({Command.CrewName})
)
Then
(
c:=c+1
);
1/c;
IP Logged
