Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
is there any way of calculating the duration between times?
So for example, for Day 3, a user logs in four times.
What I wanted to know is that between 02:15:02 and 02:32:24, what is the difference.
then I want to keep looping through the logins... so then what is the difference in hours and minutes between 02:32:24 and 09:24:37
So on and so forth...
Any ideas or tips?
Hi Sabah,
that is a date-time-stamp you have there. Those cannot be worked with directly in QlikView - first you have to split it into a date and a time, using sth. like
- DATE(Floor(num([stamp])), 'DD.MM.YYYY')
- TIME(Frac(num([stamp])), 'hh:mm:ss')
=> Then you can just subtract one time from the other and you'll get the difference.
HTH
Best regards,
DataNibbler
Hi Data Nibbler,
That works well, thank you. I am doing a calculation as such:
=hour(max(TimeTest) - min(TimeTest)) & ' hours ' & minute(max(LOG_TIME, Date) - min(LOG_TIME, Date)) & ' minutes'
I can now calculate the difference, but as you can see, its showing the total duration between 08:51:18 and 13:36:42.
It is not showing the difference between 08:51:18 and 09:10:41 AND THEN 09:10:41 and 13:36:42.
Hi,
In the load script load another table and with help of Interval function get the difference in the time for the first and last time stamp for the difference.
tmp:
LOAD Day,%Key,Timestamp(timestamp#(LOG_TIME,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as LOG_TIME,ID;
LOAD * Inline
[
Day,%Key,LOG_TIME,ID
3,13828,03/11/2014 02:15:02,1
3,13828,03/11/2014 02:32:24,1
3,13828,03/11/2014 09:24:37,1
3,13828,03/11/2014 10:24:38,1
];
LOAD
%Key,
Timestamp(Min(LOG_TIME)) as MinTime,
Timestamp(Max(LOG_TIME)) as MaxTime,
Interval(Timestamp(Max(LOG_TIME)) - Timestamp(Min(LOG_TIME)),'hh:mm:ss' ) as Diff
Resident tmp
Group By %Key;
Regards
Anand
Hi,
Even you can do this in the front end also no need to load another table for this take the straight table
Dim1:- Day
Dim2:- %Key
Expre1:- Timestamp(Min(LOG_TIME))
Expre2:- Timestamp(Max(LOG_TIME))
Expre3:- Interval(Timestamp(Max(LOG_TIME)) - Timestamp(Min(LOG_TIME)),'hh:mm:ss')
Regards
Anand
Thanks all 🙂
Hi,
Thanks, if got correct answer close the thread with appropriate answers.
Regards
Anand
create an additional expression:
Interval(LOG_TIME - Above(LOG_TIME))
hope this helps
regards
Marco