Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Subtract Dates and Times field

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


A

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Subtract Dates and Times field

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


talk is cheap, supply exceeds demand
11 Replies
MVP & Luminary
MVP & Luminary

Re: Subtract Dates and Times field

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


talk is cheap, supply exceeds demand
Not applicable

Re: Subtract Dates and Times field

Time receivedDate received by RCMTValue Time approved / declinedDate closedCorrect HoursQlikview HoursDiff
10:00:0015/10/2012      97,295.00 1515782208:50:0020/10/2012118.5138.520
10:00:0017/10/2012 1,415,779.63 Limit Ext14:45:0023/10/2012148.45168.4520
10:00:0018/10/2012      89,033.00 1092537214:13:0018/10/20124.1324.1320
10:00:0018/10/2012      30,000.00 1585555014:50:0024/10/2012148.5168.520

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

 

MVP & Luminary
MVP & Luminary

Re: Subtract Dates and Times field

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


talk is cheap, supply exceeds demand
Not applicable

Re: Subtract Dates and Times field

Gysbert

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

I really appreciate it

Anne

Not applicable

Re: Subtract Dates and Times field

Gysbert

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

I really appreciate it

Anne

Not applicable

Re: Subtract Dates and Times field

Gysbert

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

I really appreciate it

Anne

Not applicable

Re: Subtract Dates and Times field

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

Not applicable

Re: Subtract Dates and Times field

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

MVP & Luminary
MVP & Luminary

Re: Subtract Dates and Times field

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.


talk is cheap, supply exceeds demand
Community Browser