topic Re: Calculate time difference in expression in QlikView

Calculate time difference in expression

Anonymous — Fri, 25 Oct 2013 20:59:35 GMT

Hi guys,

I have 2 time variables and I want to calculate average difference between them in pivot table.

Please see below.

How can I get it?

Thanks,

Re: Calculate time difference in expression

bdunphy — Fri, 25 Oct 2013 21:53:49 GMT

as time can just be subtracted. just use avg(time2-time1).

this should calculate correctly in a pivot table

Re: Calculate time difference in expression

Fri, 25 Oct 2013 22:59:44 GMT

Using the DateDiff () function we can know the number of days, months, years, hours, minutes and seconds between two given dates.

The format of the function is as follows:

DateDiff ("period", date1, date2)

Which period can be:

d (day)

m (month)

yyyy (year)

h (hours)

m (min)

s (seconds)

Date1 and date2 variables contain dates to subtract.

Re: Calculate time difference in expression

preminqlik — Sat, 26 Oct 2013 00:48:44 GMT

Interval(cOMEDATETIME-SUBMITDATETIME,'d HH:MM')

Re: Calculate time difference in expression

Sat, 26 Oct 2013 03:28:56 GMT

Hi,

It seems, you need the difference between submit & come DateTime in min's. so, use the below expression:

=Interval(submitDateTime-comeDateTime,'mm')

Re: Calculate time difference in expression

Anonymous — Mon, 28 Oct 2013 14:15:46 GMT

Hi guys, thanks for your ideas.

It doesn't work,

I need to calculate time difference in pivot table by Months.

Please see the attached file.

I have 2 dimensions: surveyID, MYear,

the expression should be avg(time differece between comeDate Time and submitDateTime in minut)

Thanks again.

Re: Calculate time difference in expression

christian77 — Mon, 28 Oct 2013 14:22:26 GMT

Hi, here they go some solutions.

month(Date1)-month(Date2) --> Need to know years.

Set up a period by

autonumber(Year&Month) as Period_id

Year&Month as Period

then count Periods.

(Date1-Date2) / 30 --> Aproximate.

count({$<Date={'>=Date1<=Date2'}>} distinct Year&Month) --> Every month involved.

Re: Calculate time difference in expression

christian77 — Mon, 28 Oct 2013 14:30:30 GMT

Hi:

also:

avg(aggr(nodistinct Date1-Date2, RespondentID)))

Re: Re: Calculate time difference in expression

preminqlik — Mon, 28 Oct 2013 14:39:35 GMT

hi there,

find attachment

hope this helps you

Regards

Premhas

Re: Calculate time difference in expression

Anonymous — Mon, 28 Oct 2013 14:42:12 GMT

thanks, but it didn't work

Re: Re: Calculate time difference in expression

Anonymous — Mon, 28 Oct 2013 14:46:31 GMT

it shows wrong results.

In first table I have count for each surveyID by month, time difference should be for these moths

Regards,

Re: Calculate time difference in expression

javier_florian — Mon, 28 Oct 2013 14:57:58 GMT

Hi Vladimir,

As you have survey ID and respondent ID, you must calculate an average by respondent like this:

=Interval#(Avg(comeDateTime)-Avg(submitDateTime))

Or, call in pivot table your respondent ID to avoid use an average function.

- Javier Florian

Re: Calculate time difference in expression

Anonymous — Mon, 28 Oct 2013 15:18:00 GMT

Thanks.

100 points

Re: Re: Calculate time difference in expression

preminqlik — Mon, 28 Oct 2013 15:44:30 GMT

hey ,there ,

now its coming correct....:)

find attachment

Re: Re: Calculate time difference in expression

Anonymous — Mon, 28 Oct 2013 15:48:23 GMT

Javier_Florian helped with idea.

Please see above his expression