Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Workout time difference between two columns

Hi,

I have a straight chart which currnetly houses 3024 rows of data from a SQL command.  There are two fields in this straight chart called 'logdate' and 'fixby'.

I want to create another column at the end of the straight chart which basically gives me the difference in days:hh:mm:ss between the logdate and the fixby date.

Firstly I would like to know how to create a new column at the end of the straight chart

Secondly I would like the know what code or command you would use to calculate the difference between the fixby date and the log date.  Also at the same time I would like this to ignore the following times: before 08:00, after 18:00, and all day Saturday and Sunday.  Therefore I only want it calculate hours, mins and seconds, aslong as it is between 08:00 to 18:00 - Monday to Friday.

Regards,

Jon

2 Replies
Not applicable
Author

Hi,

I guess you mean the Chart "Straight Table". Then you can add a new expression.

If you mean the "Table Box" it is not possible to add a calculated field.

if you want to get the difference between two dates / time, you should try to use the numeric value of the date/time.

It will look quite strange, but very nice to calculate with (i.e. 41544.55555 ).

after calculation you can convert it back to a real date/time.

if you only want to take care of mon-fri try weekday(). Returns 0-6 and you should use only the days you want.

for the times: just check the time function and use it for compares. (09.00 am is 0.375 according to help ).

hth

Not applicable
Author

I have entered the following code into my expression on chart properties.

= NetWorkDays(logdate,fixby)-1 * ('18:00'-'08:00')-MOD(logdate,1)+MOD(fixby,1)

which unfortunately does not bring anything up. (It was how I used to work it out in excel)

However if I only enter the following code:

= NetWorkDays(logdate,fixby)-1 * ('18:00'-'08:00')

It seems to shopw 14:00:00 in each column.  What do I need to enter after the (08:00') to ensure it calculate the logdate by the fixby to get the amount of hours a call has been outstanding.