Converting Date/Time to Seconds

[Former Member]

I have a report where I need to calculate the Avg Logged-In Time for a rep.

I got to the part where I take their log-in and log-out information to calculate the Logged-In duration. Now I need to add the duration and then divide it by the number of log-Ins.

Converting to Seconds- @ AgentstateTime

tonumber(left(totext(left(totext(time({@GMTTOCST}),"HH:mm:ss"),8)),2))3600+ tonumber(mid(totext(left(totext(time({@GMTTOCST}),"HH:mm:ss"),8)),4,2))60+ tonumber(right(totext(left(totext(time({@GMTTOCST}),"HH:mm:ss"),8)),2))

Duration

Next(AgentStateTime)-(AgentStateTIme)

LOG Duration AgentStateTime

Logged out 48 48577

Logged in 31 48,625

Logged out 27 48,656

Logged in 324 48,683

Logged out 22 49,007

I would like to add the duration field where LOG=Logged in then divided by the number of sessions.

(31+324) /2

My problem is it does not let me do a sum or a running total on the "Duration" field. Is there another way of achieving the same result?

Appreciate any help!

Thanks!

Achett13

[abhilash_kumar · Accepted solution]

Hi Achett,

I'm sure 'Duration' is a 'whileprintingrecords' formula and hence it wouldn't let you create a running total or a summary off it.

You can, however, create an array and store the 'Duration'(s) for just the Logged in times.

For eg:

whileprintingrecords;
numbervar array arr;
numbervar i;
if ({LOG} = "Logged in") then
(
   i := i + 1;
   redim preserve arr<i>; 
   arr<i> := {Duration_field_or_formula};
);
""

Place this formula on the same section that displays the Duration, maybe towards the end

Create another formula to find the average:

whileprintingrecords;
numbervar array arr;
numbervar j;
numbervar count;
for j := 1 to ubound(arr) do
  c := c + arr[j];
c/ubound(arr);

Place this formula on the report footer/page footer.

If the report is grouped on the Agent_Id or Name, then you would need to reset all the variables and the array used in the above formulas to zero at the group header.

Let me know how this goes!

-Abhilash

[abhilash_kumar · Accepted solution]

Hi Achett,

Another easy way of doing it!

Right-click the Duration field > select > Format field > click on the formula button beside 'Display String' :

numbervar sum_logged_in;
numbervar inc;
if {LOG} = "Logged in" then
(
  sum_logged_in := sum_logged_in + currentfieldvalue;
  inc := inc + 1;
);
totext(currentfieldvalue,0);

Now, Create a formula to display the Average;

whileprintingrecords;
numbervar sum_logged_in;
numbervar inc;
sum_logged_in/inc;

Again, you would need to reset the variables at the group level, if at all the report has groups.

-Abhilash

[Former Member · Accepted solution]

this works great!!! I just have to figure out how to reset the value at Agent Id group.

Thanks!

Achett13

[abhilash_kumar · Accepted solution]

Glad it works!

Let me know if you face any issues resetting the variables.

-Abhilash

[Former Member · Accepted solution]

I created a formula called:

@Reset

Whileprintingrecords;

Numbervar Counter:= 0

I placed it in the Group Header. Then I tried this in the Avg Logged-In formula

@Avg Logged-In

whileprintingrecords;

numbervar Counter;

numbervar sum_logged_in;

numbervar inc;

If inc<> 0 then

*Counter:= Counter+sum_logged_in/inc * (This of course does not give me what I want)

At this point I'm a lost soul I do not have much experience writing these codes.

As far I can understand, I should create a counter and then should be able to use that to calculate the Avg Logged-In so it resets at the group level.

[abhilash_kumar · Accepted solution]

Hi Achett,

Well, I posted two methods; the 1st method requires you to create formulas from the field explorer and the second one requires you to create a 'Display String' formula for the 'Duration' field and another formula for the Average that needs to be created from the Field explorer.

Which method do you prefer?

If you prefer the 1st method, then I would like to correct something in the Average formula that I posted. I forgot to refer the correct variables. It should be:

whileprintingrecords;
numbervar array arr;
numbervar j;
numbervar count;
for j := 1 to ubound(arr) do
  count := count + arr[j];
count/ubound(arr);

This method as posted would require arrays as well and I've posted that formula too.

At the group header you would need to reset all the variables used in the two formulas. maybe you could create a simple formula with all the variables set to zero, which I believe has been done, however you would need to add all the other variables too.

-Abhilash

[Former Member · Accepted solution]

Abhilash,

I used the second method where I created a "Display String" formula and Avg Logged-In formula.

Thanks!

Achett13

[abhilash_kumar · Accepted solution]

Alright, if you're using the Display String method then the formula to calculate the average is:

whileprintingrecords;
numbervar sum_logged_in;
numbervar inc;
sum_logged_in/inc;

So, the formula to reset the variables would be:

whileprintingrecords;
numbervar sum_logged_in := 0;
numbervar inc := 0;

In the formula that you posted, I also see a variable called 'counter'. We haven't used this variable anywhere in the 2nd method.

Maybe, that's the reason you're not getting the right average.

-Abhilash

[Former Member · Accepted solution]

Abhilash,

This works great!

I used the "Counter" variable as a reset....my assumption was to use it again in the Avg Logged-In....I was just testing to see what would come of it.

To reset I used...

Whileprintingrecords;

numbervar Counter:= 0

Using the reset formula you posted worked. Appreciate all the help you provided.

Thanks again

Achett13

[abhilash_kumar · Accepted solution]

Awesome!

[Former Member]

What is the format for your date_Time ?

[Former Member]

The original "EventDate/Time" is in Date/Time Format (GMT) which I had to convert to (CST) in Date/Time format.