topic Re: Workout time difference between two columns in QlikView

Workout time difference between two columns

Thu, 23 Aug 2012 11:52:46 GMT

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

Re: Workout time difference between two columns

Thu, 23 Aug 2012 12:33:19 GMT

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

Re: Workout time difference between two columns

Thu, 23 Aug 2012 13:33:06 GMT

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.