Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi.
I have the following User Demand:
There is a table (attached) with Salesman, Customer, Date, Visit ID, Start Time and End Time.
I need to calculate the duration of the visit of the salesman made to a customer.
To begin with, there is an option that there will be more than one visit per day.
Now, let's assume that there are 2 visits per day:
If the second visit starts within 15 Min of the end of the first visit - I have to look at both visit as one and to take the start time from the first visit and the end time of the second visit and this is the Duration.
If the second visit start after 15 min. It means that they are separate visits and then I have to sum the duration of each visit
In the example attached:
For the 08.08.2018 the duration will be 60 Min.
For the 09.08.2018 the duration will be 45 Min.
Again, there could be one, two or more visits per day.
How can I achieve it (either in the script or GUI?)
Thx.
Motty
Try this
Table: LOAD [Sales Man], Customer, [Visit ID], Date(Date#(Date, 'MM.DD.YYYY')) as Date, [Start Time], [End Time] FROM [..\..\Downloads\Visit Duration (1).xlsx] (ooxml, embedded labels, table is גיליון1); FinalTable: LOAD *, If([Sales Man] = Previous([Sales Man]) and Customer = Previous(Customer) and Date = Previous(Date), If([Start Time] - Previous([End Time]) <= MakeTime(0, 15), Peek('New Start Time'), [Start Time]), [Start Time]) as [New Start Time], If([Sales Man] = Previous([Sales Man]) and Customer = Previous(Customer) and Date = Previous(Date), If([Start Time] - Previous([End Time]) <= MakeTime(0, 15), Peek('New Visit ID'), [Visit ID]), [Visit ID]) as [New Visit ID] Resident Table Order By [Sales Man], Customer, Date, [Visit ID]; DROP Table Table;
May be a hybrid of script + UI based solution
Script
Table: LOAD [Sales Man], Customer, [Visit ID], Date(Date#(Date, 'MM.DD.YYYY')) as Date, [Start Time], [End Time] FROM [..\..\Downloads\Visit Duration.xlsx] (ooxml, embedded labels, table is גיליון1); FinalTable: LOAD *, If([Sales Man] = Previous([Sales Man]) and Customer = Previous(Customer) and Date = Previous(Date), If([Start Time] - Previous([End Time]) <= MakeTime(0, 15), Previous([Start Time]), [Start Time]), [Start Time]) as [New Start Time], If([Sales Man] = Previous([Sales Man]) and Customer = Previous(Customer) and Date = Previous(Date), If([Start Time] - Previous([End Time]) <= MakeTime(0, 15), Previous([Visit ID]), [Visit ID]), [Visit ID]) as [New Visit ID] Resident Table Order By [Sales Man], Customer, Date, [Visit ID]; DROP Table Table;
Front end
Dimension
Date
Expression
Interval(Sum(Aggr(Max([End Time]) - [New Start Time], Date, [New Visit ID])), 'mm')
Works fine
Thanks a lot
Found a hole in the loop...
See File attached... when there are more than 2 visits in the same time frame
Hi.
The expected output would be that the first 3 rows would be considered as one visit. which means same new VisitID, New starttime from the first row, NewendTime from the third row. Total duration of two hours.
there may be a case where i would need to combine 4 rows and even 5. i dont know it in advance.
Thx
Try this
Table: LOAD [Sales Man], Customer, [Visit ID], Date(Date#(Date, 'MM.DD.YYYY')) as Date, [Start Time], [End Time] FROM [..\..\Downloads\Visit Duration (1).xlsx] (ooxml, embedded labels, table is גיליון1); FinalTable: LOAD *, If([Sales Man] = Previous([Sales Man]) and Customer = Previous(Customer) and Date = Previous(Date), If([Start Time] - Previous([End Time]) <= MakeTime(0, 15), Peek('New Start Time'), [Start Time]), [Start Time]) as [New Start Time], If([Sales Man] = Previous([Sales Man]) and Customer = Previous(Customer) and Date = Previous(Date), If([Start Time] - Previous([End Time]) <= MakeTime(0, 15), Peek('New Visit ID'), [Visit ID]), [Visit ID]) as [New Visit ID] Resident Table Order By [Sales Man], Customer, Date, [Visit ID]; DROP Table Table;
Now it seems to be working.
Can you please explain me the difference? How does Peek works in such a case?
Totaly
Very Elegant solution
Thx