topic Re: Calculating Time Duration With Conditions in App Development

Calculating Time Duration With Conditions

sogloqlik — Mon, 10 Dec 2018 11:27:34 GMT

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

Re: Calculating Time Duration With Conditions

sunny_talwar — Mon, 10 Dec 2018 12:29:58 GMT

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

Re: Calculating Time Duration With Conditions

sogloqlik — Mon, 10 Dec 2018 13:38:25 GMT

Works fine

Thanks a lot

Re: Calculating Time Duration With Conditions

sogloqlik — Wed, 12 Dec 2018 09:35:52 GMT

Found a hole in the loop...

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

Re: Calculating Time Duration With Conditions

sunny_talwar — Wed, 12 Dec 2018 12:46:38 GMT

What is the expected output in this case?

Re: Calculating Time Duration With Conditions

sogloqlik — Wed, 12 Dec 2018 12:57:09 GMT

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

Re: Calculating Time Duration With Conditions

sunny_talwar — Wed, 12 Dec 2018 13:07:49 GMT

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;

Re: Calculating Time Duration With Conditions

sogloqlik — Wed, 12 Dec 2018 13:21:47 GMT

Now it seems to be working.

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

Re: Calculating Time Duration With Conditions

sunny_talwar — Wed, 12 Dec 2018 13:24:50 GMT

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?

Re: Calculating Time Duration With Conditions

sogloqlik — Wed, 12 Dec 2018 13:34:14 GMT

Totaly

Very Elegant solution

Thx