Count Formula Question

[BradCustom]

Hi all,

I have a Excel Spreadsheet that I'm writting a Crystal Report on using Crystal XI. The question/problem I have is one of the fields in the Excel Spreadsheet is a Time field and when I bring it into Crystal XI its converted into a Date Time field. I can change the way it displays easy enough but I'd like to write a Count formula to count the number os sessions that are greater than 1:00 minute. Below is my attempt.

if {'Session_logs_2008_'.Duration} > 01:00 then
Count ({'Session_logs_2008_'.Date},{'Session_logs_2008_'.Search Term})
else 0

Thanks for your help!

 

[Madawc]

If you already have duration, modify the formula you have:

if {'Session_logs_2008_'.Duration} > 01:00 then 1
else 0

Place this at detail line level and do a count on it. Or you could do a running total with a formula - see FAQ767-6524 if you're not already familiar with them.

If you need to find the difference in time between two date/time fields, DateDiff does this and can be set to count minutes. Look it up in your Crystal's HELP, if you have it.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

How does the duration field actually display (with no extra formatting)? Is it a string?

Also your conditional formula just says that if the current value meets the criteria, show me the count for all all members of the group, so you would need to use a formula like Madawc suggested, except that your duration value will not work as shown.

-LB

 

[BradCustom]

The duration field without formatting displays as a date time field (example 1/1/1899 12:01:00 AM).

I found a problem with the Excel spreadsheet which is an output from another application. It seems to surpress the beginning of the time field so 1 minute is 01:00 in stead of 00:01:00. This causes a problem with any formula so I'm in the process of fixing all the fields. Once this is fixed I can use a simple greater than formula to count all fields greater than 12:01:00 AM.

Thanks, I knew my example didn't work it saw the : and thought that the "then" statement should be next. I only wanted to count the number of true statements for each group.

Once I've fixed the field I'll let you know how everything works.

Thanks for your help!

 

[BradCustom]

I've fixed the field in Excel and fixed the formula in Crystal but I'm still doing something wrong. Below is my attempt to count all instances where the duration is greater than 12/30/1899 12:01:00 AM.

if ({'Session_logs_2008_'.Duration}) > #12/30/1899 12:01:00# then 
Count ({'Session_logs_2008_'.Date},{'Session_logs_2008_'.Search Term})
else 0

Thanks for your help!

 

[lbass]

How can a datetime field show duration, which implies elapsed time?

As I said earlier,

Also your conditional formula just says that if the current value meets the criteria, show me the count for all members of the group.

You need to use a formula like this:

if ({'Session_logs_2008_'.Duration}) > #12/30/1899 12:01:00# then 1

Then insert a sum on this formula at the search term group level.

-LB

 

[BradCustom]

The field is determined by the application I'm working with, I'm guessing that they do a datediff formula to get the duration. However when you output the data the field is formated as a datetime field (that's why all of the entries are 12/30/1899)and the duration is shown as a departure from 12/30/1899 12:00:00. It doesn't make any sence to me either but it's what I have to work with.

Thanks for your help!