topic Re: Subtract Dates and Times field in QlikView

Subtract Dates and Times field

Thu, 20 Dec 2012 11:44:49 GMT

Hi Experts

I hope you can help :

I am loading data in from an Excel spreadsheet , I wish to be able to calculate the Turnaround time of a Task.

I have the following Columns

hh:mm dd/mm/yyyy hh:mm dd/mm/yyyy

[Start Time] [Start Date] [End Time] [End date]

8:00 1/1/12 10:00 1/1/12

8:00 1/1/12 11:00 1/2/12

8:00 1/1/12 11:30 1/2/12

What I'd like is to have nother column that does the calc - so see below with desired results in Hours:Minutes Format

hh:mm dd/mm/yyyy hh:mm dd/mm/yyyy hh:mm

[Start Time] [Start Date] [End Time] [End date] [turnaround]

8:00 1/1/12 10:00 1/1/12 2:00

8:00 1/1/12 11:00 1/2/12 27:00

8:00 1/1/12 11:30 1/2/12 27:30

Not sure if its best to do as part of the load or if we should calc in a Chart

Can anyone advise how I may do this please ?

Thanks

Re: Subtract Dates and Times field

Gysbert_Wassenaar — Thu, 20 Dec 2012 12:01:28 GMT

It's best to calculate it in the script as part of the load. Depending on how the times and dates are stored in excel you need to determine how you can calculate the turnaround. Maybe it's possible to simply add Start Date and Start Time to get a timestamp. If you're not that lucky you may need to use the timestamp# function, e.g. something like timestamp#( [Start Date] & ' ' & [Start Time], 'DD/MM/YYYY hh:mm'). You can calculate the turnaround with the interval function. Try interval([End Date]+[End Time]-[Start Date]-[Start Time],'hh:mm') as turnaround

Re: Subtract Dates and Times field

Thu, 20 Dec 2012 14:13:04 GMT

Time received	Date received by RCMT	Value		Time approved / declined	Date closed	Correct Hours	Qlikview Hours	Diff
10:00:00	15/10/2012	97,295.00	15157822	08:50:00	20/10/2012	118.5	138.5	20
10:00:00	17/10/2012	1,415,779.63	Limit Ext	14:45:00	23/10/2012	148.45	168.45	20
10:00:00	18/10/2012	89,033.00	10925372	14:13:00	18/10/2012	4.13	24.13	20
10:00:00	18/10/2012	30,000.00	15855550	14:50:00	24/10/2012	148.5	168.5	20

Hi Gysbert

Thanks for the above - I was just testing it - I loaded it in the script as :

interval

([Date closed]+[Time approved / declined]-[Date received by RCMT]+[Time received],'hh:mm') as Turnaround

Then in the Table just sum(Turnaround) - It turns out the calc is incorrect by exactly 20 everytime .

Do you know why ?

Thanks
Anne

Re: Subtract Dates and Times field

Gysbert_Wassenaar — Thu, 20 Dec 2012 14:19:21 GMT

You add [Time received] instead of subtracting it.

You have:

interval

([Date closed]+[Time approved / declined]-[Date received by RCMT]+[Time received],'hh:mm') as Turnaround

which should be:

interval

([Date closed]+[Time approved / declined]-([Date received by RCMT]+[Time received]),'hh:mm') as Turnaround

which is the same as:

interval

([Date closed]+[Time approved / declined]-[Date received by RCMT]-[Time received],'hh:mm') as Turnaround

Re: Subtract Dates and Times field

Thu, 20 Dec 2012 14:28:24 GMT

Gysbert

Thanks so much -- I'll never understand how people resolve these posts !!

I really appreciate it

Anne

Re: Subtract Dates and Times field

Thu, 20 Dec 2012 14:28:29 GMT

Gysbert

Thanks so much -- I'll never understand how people resolve these posts !!

I really appreciate it

Anne

Re: Subtract Dates and Times field

Thu, 20 Dec 2012 14:32:20 GMT

Gysbert

Thanks so much -- I'll never understand how people resolve these posts !!

I really appreciate it

Anne

Re: Subtract Dates and Times field

Fri, 21 Dec 2012 11:19:08 GMT

Hi Gysbert

The above is how I requested - thanks a mill

Just wondering is it possible to format the same as days - see below

Thanks so much

Anne

hh:mm dd/mm/yyyy hh:mm dd/mm/yyyy hh:mm Days

[Start Time] [Start Date] [End Time] [End date] [turnaround] [ turnaround]

8:00 1/1/12 10:00 1/1/12 2:00 0.08

8:00 1/1/12 11:00 1/2/12 27:00 1.125

8:00 1/1/12 11:30 1/2/12 27:30 1.145

Re: Subtract Dates and Times field

Fri, 21 Dec 2012 11:30:37 GMT

Hi Gysbert

The above is how I requested - thanks a mill

Just wondering is it possible to format the same as days - see below

Thanks so much

Anne

hh:mm dd/mm/yyyy hh:mm dd/mm/yyyy hh:mm Days

[Start Time] [Start Date] [End Time] [End date] [turnaround] [ turnaround]

8:00 1/1/12 10:00 1/1/12 2:00 0.08

8:00 1/1/12 11:00 1/2/12 27:00 1.125

8:00 1/1/12 11:30 1/2/12 27:30 1.145

Re: Subtract Dates and Times field

Gysbert_Wassenaar — Fri, 21 Dec 2012 12:12:36 GMT

In a chart you can change the number format on the Numbers tab of the properties screen. Set it to Fixed and specify the number of decimals you want. So you can use the same field and only change the format for presentation.

Re: Subtract Dates and Times field

Fri, 21 Dec 2012 12:29:59 GMT

Thanks so much

Re: Subtract Dates and Times field

Fri, 21 Dec 2012 12:30:29 GMT

Thanks so much