Solved

Crystal Datediff

Posted on 2014-04-24
16
243 Views
Last Modified: 2014-06-06
I forgot how to convert a date to DD:HH:MM:SS using datediff.  This is what I have.

If {Incident.Status} = "Active" then Datediff("d",{Incident.CreatedDateTime},currentDateTime)

It is showing 17:00.  I used the customized under the format field to get the ":"
0
Comment
Question by:cdmccoy
  • 8
  • 5
  • 2
  • +1
16 Comments
 
LVL 15

Expert Comment

by:Ess Kay
ID: 40020784
time({Incident.CreatedDateTime} - currentDateTime)
then format the resulting formula field to use the "13:23" format option
 
 
...As long as you do not have any result minutes > 24 hours
0
 

Author Comment

by:cdmccoy
ID: 40020838
I used If {Incident.Status} = "Active" then time({Incident.CreatedDateTime} - currentDateTime)

Then using format "13:23" and the result for the time is showing incorrect.  The incident.createdDateTime is 4/7/2014 2:07PM  to current date.  the result should be 17:XX:XX:XX
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40020972
WHat do you want returned from the DateDiff?

DateDIff with d as the units only returns the number of FULL days between the dates so yours is saying there are 17 days.

To get what you want use this formula

Local DateTimeVar testdate := {Incident.CreatedDateTime};
Local NumberVar numDays;
Local NumberVar numHours;
Local NumberVar numMinutes;
Local NumberVar numSeconds;

numSeconds := DateDiff('s',testdate,CurrentDateTime);

numDays := numSeconds \ 86400;  // Number of seconds in a day (24 * 60 * 60)
numSeconds := numSeconds mod 86400;

numHours := numSeconds \ 3600;
numSeconds := numSeconds mod 3600;

numMinutes := numSeconds \ 60;
numSeconds := numSeconds mod 60;

CStr(numDays,0) & ':' & CStr(numHours,"00") & ':' & CStr(numMinutes,"00") & ':' & CStr(numSeconds,"00")

Open in new window


mlmcc
0
 

Author Comment

by:cdmccoy
ID: 40021031
Yes -- That is exactly what I needed.  If I wanted to group on this value to obtain the average age of the entire group, what variable would I use to sum... would it be testdate
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40021240
Are you wanting to group on it or to get the average.  The 2 are very different

Since the value returned by the formula is a string, there is no way to average it.  That would take a different formula

mlmcc
0
 

Author Comment

by:cdmccoy
ID: 40021290
I would need to group on it.  The individual ticket duration open is good what you sent me, however I need to group on Team so they will just see the overral avg of their incident that are remaining open
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40021295
So you want to group an the team field then show the average age in the group footer?

mlmcc
0
 

Author Comment

by:cdmccoy
ID: 40105549
Yes.  I want to group on the team field and if the tickets are active within a week I want an average time duration of how long the ticket has been open within that week.  The top formula breakes down the days/hours/min/sec which is great but it does not cumulate the total average age.  Thanks for your assistance.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 100

Expert Comment

by:mlmcc
ID: 40106881
So you only need to see the average for tickets that are less than 7 days old?

mlmcc
0
 

Author Comment

by:cdmccoy
ID: 40106904
I need to see all active assignments starting with the Task.CreationDate to CurrentDateTime to see the average time for the team.  The report is grouped on Team.

here is the formula I am using to obtain the total Count of "Active" for the Team.  In most cases the ?Assignment Date is CurrentDateTime.  The task.status "Accepted", "Waiting" indicates the assignment is still open.  Therefore I need to know the Total Average the assignment has been open.

If {Task.CreatedDateTime} <= {?Assignment Date} and
  {Task.Status} in ["accepted", "Waiting"]
0
 

Author Comment

by:cdmccoy
ID: 40106909
Sorry for any confusion.  At first I was told for the 7 days but they want to see the total average duration of all assignments that are open
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 500 total points
ID: 40108172
You used Incident.Status, Incident.CreatedDateTime and CurrentDateTime in your earlier posts, and Task.Status, Task.CreatedDateTime and {?Assignment Date} in your recent posts.  I'm assuming that the recent posts are correct, but wanted to mention that difference, in case you're actually looking at two different sets of fields (Incident and Task).


 If you're doing a sum on a formula like the following to get the count of active assignments:

If {Task.CreatedDateTime} <= {?Assignment Date} and
 {Task.Status} in ["accepted", "Waiting"] then
  1
else
  0

 Then I think you could use a formula like this:

If {Task.CreatedDateTime} <= {?Assignment Date} and
 {Task.Status} in ["accepted", "Waiting"] then
  DateDiff ("s", {Task.CreatedDateTime}, {?Assignment Date})
else
  0

 That should give you the seconds for each active assignment.  Then you could do a summary on that to get the total seconds for the active assignments, and divide that by the count of active assignments from the first formula.  Something like this:

if Sum ({@first formula above}, {Team group field}) <> 0 then
  Sum ({@second formula above}, {Team group field}) / Sum ({@first formula above}, {Team group field})


 That should give you the average number of seconds for the active assignments for the team, which you could then convert to dd:hh:mm:ss format using a formula like the one that mlmcc posted earlier.

 James
0
 

Author Comment

by:cdmccoy
ID: 40108933
James -- That is great with the exception I need to get a summary count of the Total Volume which gives me

If {Task.CreatedDateTime} <= {?Assignment Date} and
 {Task.Status} in ["accepted", "Waiting"] then
  1
else
  0

within my running total.  I need the cumulation of the avg age of the Task.Creation date to CurrentDateTime in DD:HH:MM:SS format.  Attached please see the data from the Wireless Team -- I highlighted the COUNT in RED.  The Task.Creation Date to CurrentDateTime should be the Ave of the "Active" incidents.  To make it simple, there are only 3 ACTIVE in this group.  Look at the Task.CreationDate -- The total days are only 20 days old.  I need the Avg from that total which is 10:HH:MM:SS.  I hope that makes sense.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40108961
Do the formulas I added to your other question help with this one?

mlmcc
0
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
ID: 40109443
There was nothing attached to your last post.

 I don't know what you mean by "a summary count of the Total Volume".  If there is one record per task, then a sum of the formula in your last post, for the group, should give you a count of the active tasks for the group.

 What do you mean by "cumulation of the avg age"?  Are you just trying to get the average time for the active tasks for each team; or are you trying to calculate the average for each team, and then get a total of those averages?  "Accumulating the average" kind of suggests the latter.

 James
0
 

Author Comment

by:cdmccoy
ID: 40117991
The team work was awsome.  I would not have gotten this report completed without both assistance.... Thank you both so much.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now