# 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
Did you mean:
Highlighted
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.

How can I get it?

Thanks,

1 Solution

Accepted Solutions
Highlighted
Creator III

## Re: Calculate time difference in expression

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

14 Replies
Highlighted
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

## 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

## 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.

I have 2 dimensions: surveyID, MYear,

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

Thanks again.

Highlighted
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

## Re: Calculate time difference in expression

Hi:

also:

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

Highlighted
Specialist II

## Re: Re: Calculate time difference in expression

hi there,

find attachment

hope this helps you

Regards

Premhas

Highlighted
Specialist II

## Re: Calculate time difference in expression

thanks, but it didn't work