topic Re: How to sum an expression with 'interval' command in QlikView

How to sum an expression with 'interval' command

Sun, 30 Nov 2014 07:02:38 GMT

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?

Re: How to sum an expression with 'interval' command

its_anandrjs — Sun, 30 Nov 2014 07:10:56 GMT

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

Re: How to sum an expression with 'interval' command

Anonymous — Sun, 30 Nov 2014 08:56:47 GMT

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

Re: How to sum an expression with 'interval' command

maxgro — Sun, 30 Nov 2014 09:22:12 GMT

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

sum(CloseDate-FirstContactDate)

see attachment

Re: How to sum an expression with 'interval' command

Sun, 30 Nov 2014 10:13:39 GMT

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.