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;