# Qlik Sense App Development

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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
MVP

## Re: Calculating Time Duration With Conditions

Try this

```Table:
Customer,
[Visit ID],
Date(Date#(Date, 'MM.DD.YYYY')) as Date,
[Start Time],
[End Time]
FROM
(ooxml, embedded labels, table is גיליון1);

FinalTable:
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;```

9 Replies
MVP

## Re: Calculating Time Duration With Conditions

May be a hybrid of script + UI based solution

Script

```Table:
Customer,
[Visit ID],
Date(Date#(Date, 'MM.DD.YYYY')) as Date,
[Start Time],
[End Time]
FROM
(ooxml, embedded labels, table is גיליון1);

FinalTable:
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')`
New Contributor II

## Re: Calculating Time Duration With Conditions

Works fine

Thanks a  lot

New Contributor II

## Re: Calculating Time Duration With Conditions

Found a hole in the loop...

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

MVP

## Re: Calculating Time Duration With Conditions

What is the expected output in this case?
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

MVP

## Re: Calculating Time Duration With Conditions

Try this

```Table:
Customer,
[Visit ID],
Date(Date#(Date, 'MM.DD.YYYY')) as Date,
[Start Time],
[End Time]
FROM
(ooxml, embedded labels, table is גיליון1);

FinalTable:
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;```

New Contributor II

## Re: Calculating Time Duration With Conditions

Now it seems to be working.

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

MVP

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

## Re: Calculating Time Duration With Conditions

Totaly

Very Elegant  solution

Thx