This topic contains 6 replies, has 0 voices, and was last updated by adallaway 7 years, 9 months ago.

  • Author
    Posts
  • #5002

    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.

  • #5003

    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

  • #5004

    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!

  • #5005

    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)

  • #5006

    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.

  • #5007

    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

  • #5008

    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.

You must be logged in to reply to this topic.