Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
as time can just be subtracted. just use avg(time2-time1).
this should calculate correctly in a pivot table
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.
Interval(cOMEDATETIME-SUBMITDATETIME,'d HH:MM')
Hi,
It seems, you need the difference between submit & come DateTime in min's. so, use the below expression:
=Interval(submitDateTime-comeDateTime,'mm')
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.
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.
Hi:
also:
avg(aggr(nodistinct Date1-Date2, RespondentID)))
hi there,
find attachment
hope this helps you
Regards
Premhas
thanks, but it didn't work