Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist II
Specialist II

Calculate time difference in expression

Hi guys,

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

Please see below.

Capture.JPG.jpg

How can I get it?

Thanks,

1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

Re: Calculate time difference in expression

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

View solution in original post

14 Replies
Highlighted
Contributor III
Contributor III

Re: Calculate time difference in expression

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

this should calculate correctly in a pivot table

Highlighted
Not applicable

Re: Calculate time difference in expression

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.

Highlighted
Specialist II
Specialist II

Re: Calculate time difference in expression

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

Highlighted
Not applicable

Re: Calculate time difference in expression

Hi,

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

=Interval(submitDateTime-comeDateTime,'mm')

Highlighted
Specialist II
Specialist II

Re: Calculate time difference in expression

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.

Highlighted
Partner
Partner

Re: Calculate time difference in expression

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.

Highlighted
Partner
Partner

Re: Calculate time difference in expression

Hi:

also:

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

Highlighted
Specialist II
Specialist II

Re: Re: Calculate time difference in expression

hi there,

find attachment

hope this helps you

Regards

Premhas

Highlighted
Specialist II
Specialist II

Re: Calculate time difference in expression

thanks, but it didn't work