Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
Gysbert
Thanks so much -- I'll never understand how people resolve these posts !!
I really appreciate it
Anne
Gysbert
Thanks so much -- I'll never understand how people resolve these posts !!
I really appreciate it
Anne
Gysbert
Thanks so much -- I'll never understand how people resolve these posts !!
I really appreciate it
Anne
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
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
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.