I have a field that I want to get the average and median of. That field is simply
DateDiff ('s', {Tasks.St}, {#LInMin}))/60

How can I summarize it's data? Directly or indirectly by using another field that references it.

Note: if your wondering why I get the date difference in seconds then /60 to get the minutes is that using the n (minute) option of datediff rounds so I would only get 4 minutes instead of 4.53

OK sounds great, how do I do that?



The Information for scheduled hours can be summarized and is, but the worked data can't. The reason being is the worked times are actually Running Totals for min/max as the people can login/logout multitple times in their shift so it pulls the min time and max time for start/stop of worked times.

So I can't summarize to do a count function or sum and do an average that way nor can I do a straight up average function (either running total fields, or a standard formula field)

EDIT: Oh and I know GF4 looks suppressed, but it is actually a conditional suppress, Suppressing if there is no worked data for that day.

EDIT2: Here to help a bit more

LInMin is a minimum of LIn
Lin formula is {@LOut}-({@#Mins}/1440)
LOut formula is DateTimeValue ((({mOpLogout.Timestamp}/1440)+1))
#Mins formula is (({mOpLogout.LoggedIn}/1000)/60)

A few explantions.

A. The Software they use when working only stores log in information when they log out, and doesn't store and actual logged in time, as I've done you figure that out by subtracting the log out time from the amount of time they were logged in.
B. The timestamp for when they log out (mOpLogout.Timestamp)  is in minutes and actually start 1 day behind what most date serials start ( I Think most start 1/31/1900, this one starts 1/30/1900 or whatever the actual date is)
C. The logged in time(mOpLogout.LoggedIn) is in milliseconds

The fields for the text where they are tardy/late left early/worked late and the actual amount of time are if statements, they all 4 use basically the below with two differences, what times are being compared, and whether they are just a string or a date/time formula. the text ones just have late, tardy, or error, where as the actual number use the below formula just replacing the data fields depending on which it is.

If (DateDiff ('s', {Tasks.St}, {#LInMin}))/60 >= 200 or (DateDiff ('s', {Tasks.St}, {#LInMin}))/60 <= -200 Then
0.000005
Else IF (DateDiff ('s', {Tasks.St}, {#LInMin}))/60 >=0 Then
(DateDiff ('s', {Tasks.St}, {#LInMin}))/60
Else
-1 * ((DateDiff ('s', {Tasks.St}, {#LInMin}))/60)


Edited by JamesS - 19 Nov 2009 at 10:05am

This is the field that does the math:

shared numbervar ArvMt;
local numbervar ArvMs;
If (DateDiff ('s', {Tasks.St}, {#LInMin}))/60 >= 240 or (DateDiff ('s', {Tasks.St}, {#LInMin}))/60 <= -240 Then
local numbervar ArvMs:=0.000005
Else IF (DateDiff ('s', {Tasks.St}, {#LInMin}))/60 >=0 Then
local numbervar ArvMs:=(DateDiff ('s', {Tasks.St}, {#LInMin}))/60
Else
local numbervar ArvMs:= -1 * ((DateDiff ('s', {Tasks.St}, {#LInMin}))/60)

Here is the ArvMs formula field for the local value
local numbervar ArvMs;

yet it just shows a 0

what am I doing wrong?


Edited by JamesS - 25 Nov 2009 at 9:45am