How to sum an expression with 'interval' command

[#1]

Hi all, I am fairly new to QV, need help on a sum command that is not working:

• I use "interval" like this and it works fine:  Interval(CloseDate-FirstContactDate, 'd').  It returns correct values for all the records, no problem.
• As soon as I add the "sum" command, it does not return any value: sum(Interval(CloseDate-FirstContactDate, 'd')).
• My ultimate goal is to calculate an average "Number of days to close a deal" as a variable so I can get this value for any combination of dimension that I click on, like sales rep, item, industry, etc.

What am I doing wrong?

tx

[its_anandrjs]

Hi,

Try to calculate the Interval expression that is Interval(CloseDate-FirstContactDate, 'd') in the backend and then use the Sum or Avg expression in the front end for the Average.

Regards

Anand

[Anonymous]

Hi Manus,

please add below line in script:

    Interval([Call Closed - Date/Time] - [Call Opened - Date/Time],'dd') as ITime

Like this:

LOAD [Call Number],

     [Call Problem Area],

     [Customer Department Name],

     [Customer Location Name],

     [Customer Organisation Name],

     [Call Status Description],

     [Call Opened - Date/Time],

     [Call Priority],

     [Call Closed - Date/Time],

     [Root Cause of Call],

     [Call Problem Reference],

     [Parent Call Number],

     [Caused by Change],

     [Customer Name],

     [Customer ID],

     [Call Modified - Date/Time],

     [Call Cleared - Date/Time],

     Interval([Call Closed - Date/Time] - [Call Opened - Date/Time],'dd') as ITime

FROM

(biff, embedded labels);

and in UI:

=Sum(ITime)

Or the function you need.

Hope this helps

Regards

Neetha

[maxgro · Accepted solution]

you can do math on date, timestamp; it should be

sum(CloseDate-FirstContactDate)

see attachment

[#5]

Tx Massimo, it worked perfectly.  I added a new field to Load Script and the added also a variable at the bottom for "sum" of that field. works 100%

so easy.

tx