Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
sogloqlik
New Contributor II

Calculating Time Duration With Conditions

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

 

1 Solution

Accepted Solutions

Re: Calculating Time Duration With Conditions

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;

image.png 

View solution in original post

9 Replies

Re: Calculating Time Duration With Conditions

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
New Contributor II

Re: Calculating Time Duration With Conditions

Works fine

Thanks a  lot

sogloqlik
New Contributor II

Re: Calculating Time Duration With Conditions

Found a hole in the loop... Smiley Happy

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

 

Re: Calculating Time Duration With Conditions

What is the expected output in this case?
sogloqlik
New Contributor II

Re: Calculating Time Duration With Conditions

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

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;

image.png 

View solution in original post

sogloqlik
New Contributor II

Re: Calculating Time Duration With Conditions

Now it seems to be working. Smiley Happy

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

Re: Calculating Time Duration With Conditions

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
New Contributor II

Re: Calculating Time Duration With Conditions

Totaly

Very Elegant  solution

ThxSmiley Happy