Calculating Time Duration With Conditions

[sogloqlik]

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

[sunny_talwar]

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')

[sogloqlik]

Works fine

Thanks a  lot

[sogloqlik]

Found a hole in the loop... 

See File attached... when there are more than 2 visits in the same time frame

[sunny_talwar]

What is the expected output in this case?

[sogloqlik]

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

[sunny_talwar · Accepted solution]

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;

 

[sogloqlik]

Now it seems to be working. 

Can you please explain me the difference? How does Peek works in such a case?

[sunny_talwar]

Peek can iteratively look at the value just created in the previous row.... so, instead of looking at Previous([Start Time])... I started looking at Peek('New Start Time'). I wanted to pull the value of Start Time not based on Start Time, but the new field that I created.

Does that make sense?

[sogloqlik]

Totaly

Very Elegant  solution

Thx