[adallaway]

Hi all,

I want to get the sum of date differences between {custrecord_wevtlog_date_ended} and {created} grouped by employee ({custrecord_employee}).

Custom Record: Event Log

EventLog.custrecord_employee = "Foo Bar"

EventLog.created = "1/23/2017 1:51 pm"

EventLog.custrecord_wevtlog_date_ended = "1/23/2017 1:51 pm"

What I really need to be able to do in the result columns is SUM({custrecord_wevtlog_date_ended} – {created}) GROUP BY {custrecord_employee}.

I have tried a whole bunch of different combinations and end up with either an unexpected error being thrown or invalid expression errors.

SUM of TO_NUMBER({custrecord_wevtlog_date_ended} – {created}) ends up returning an empty string (I think it may be ignoring the time portion of the dates).

MAX of TO_CHAR({custrecord_wevtlog_date_ended} – {created}) shows "+000000000 00:59:02.000000" which is correct but I need it SUMmed not MAX.

Thanks for the help!
This is a cached copy. Click here to see the original post.

[david.smith]

Is this in the UI or script?

{custrecord_employee} where summary type is GROUP

formulanumeric( ROUND(({custrecord_wevtlog_date_ended} – {created}), 0) ) where summary type is SUM

[adallaway]

Its via the UI. Ill try that out when I get back to the computer. (The only difference I notice is the ROUND) Thanks!

[adallaway]

If via the UI I use "Formula (Numeric)" with summary type "SUM" and formula "ROUND(({custrecord_wevtlog_date_ended} – {created}), 0)" I get "An unexpected error has occurred." when attempting to run the search.

http://imgur.com/a/Bdo3B (screenshot failed to attach)

[erictgrubaugh]

When I'm trying to troubleshoot formulas, I typically make multiple Results columns, each progressively adding a step of the formula so I can see where things are breaking down. In your case, perhaps try something like:

Column 1: Formula (Numeric): {custrecord_wevtlog_date_ended}

Column 2: Formula (Numeric): {custrecord_wevtlog_date_ended}-{created}

Column 3: Formula (Numeric): ROUND({custrecord_wevtlog_date_ended}-{created})

If you continue getting Unexpected Errors when using all of these columns, then just start by running the search with only Column 1, then progressively adding each step to see which part of the formula is causing the Unexpected Error.

[adallaway]

SUM Formula (Numeric): {custrecord_wevtlog_date_ended} = ERROR: Invalid Expression

SUM Formula (Numeric): {created} = ERROR: Invalid Expression

SUM Formula (Numeric): {custrecord_wevtlog_date_ended} – {created} = An unexpected error has occurred.

SUM Formula (Numeric): ROUND(({custrecord_wevtlog_date_ended} – {created}), 1) = An unexpected error has occurred.

SUM Formula (Numeric): TO_NUMBER( ..any of the above .. ) = No error but blank result column

Tests with MAX to see what would happen:

MAX Formula (Numeric): {custrecord_wevtlog_date_ended} = ERROR: Invalid Expression

MAX Formula (Numeric): {created} = ERROR: Invalid Expression

MAX Formula (Numeric): {custrecord_wevtlog_date_ended} – {created} = +000000000 00:59:02.000000 (this is correct for MAX)

So it looks like the date diff works when using MAX but when using SUM is is unable to sum up all of those date diffs and throws an error. What am I missing here? Thanks

[adallaway]

Got it!

It looks like INTERVAL types in oracle cannot be summed.

If I convert the date difference into seconds then I can correctly return the summed # of seconds from each diff:

SUM (86400 * (trunc({custrecord_wevtlog_date_ended}) – trunc({created})) + to_number(to_char({custrecord_wevtlog_date_ended}, 'sssss')) – to_number(to_char({created},'sssss')))

This results in the total # of seconds between all dates: 3,542.0

It's unfortunate that I can't use numtodsinterval() on top of the summed value to put it back into a more readable INTERVAL format but it'll have to do.

Thanks for your time.

[MChammaTX]

I'm not sure what's up with NetSuite outputing date differences in the format of +0000000003 01:17:58.00000. This seems to only happen with custom fields. Native date/times return a clean difference in days.

My users are asking how to get an aging based on a custom date time field. Not sure how to explain the funky output to them. I think they would look at me funny if I gave them some long SQL to convert it back to a decimal format.

[Author]

Posts