# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

New Contributor III

## How to find the time difference in minutes with respect to different days ?

I Have the following table :

I want the difference in time (Mins) for each day. As shown in the below image.

Any help would be much appreciated.

1 Solution

Accepted Solutions
MVP

## Re: How to find the time difference in minutes with respect to different days ?

Try this:

Table:

TimeStamp(RangeMax(Temp2, B)) as B,

TimeStamp(RangeMin(Temp, C)) as C,

Interval(RangeMin(Temp, C) - RangeMax(Temp2, B), 'mm') as D;

B,

C,

DayEnd(B + IterNo() - 1) as Temp,

DayStart(B + IterNo() - 1) as Temp2

While DayEnd(B + IterNo() - 1) <= DayEnd(C);

A, B, C

7_266, 10/07/2015 11:30:00 PM, 10/08/2015 7:00:00 AM

];

16 Replies
MVP

## Re: How to find the time difference in minutes with respect to different days ?

Try this:

Table:

TimeStamp(RangeMax(Temp2, B)) as B,

TimeStamp(RangeMin(Temp, C)) as C,

Interval(RangeMin(Temp, C) - RangeMax(Temp2, B), 'mm') as D;

B,

C,

DayEnd(B + IterNo() - 1) as Temp,

DayStart(B + IterNo() - 1) as Temp2

While DayEnd(B + IterNo() - 1) <= DayEnd(C);

A, B, C

7_266, 10/07/2015 11:30:00 PM, 10/08/2015 7:00:00 AM

];

New Contributor III

## Re: How to find the time difference in minutes with respect to different days ?

Thank you Sunny. This is the exact solution which i was looking for.

I do have another query. Suppose the same row had to be split into hours.

Example - 10/7/2015 11:30:00 PM - 10/7/2015 11:59:59 PM

10/8/2015 12:00:00 PM - 10/8/2015 01:00:00 PM

.......

.......

10/8/2015 6:00:00 AM - 10/8/2015 07:00:00 AM

MVP

## Re: How to find the time difference in minutes with respect to different days ?

Table:

TimeStamp(RangeMax(Temp2, B)) as B,

TimeStamp(RangeMin(Temp, C)) as C,

Interval(RangeMin(Temp, C) - RangeMax(Temp2, B), 'mm') as D;

B,

C,

TimeStamp(Ceil(B + MakeTime(IterNo() - 1), 1/24)) as Temp,

TimeStamp(Floor(B + MakeTime(IterNo() - 1), 1/24)) as Temp2

While Ceil(B + MakeTime(IterNo() - 1), 1/24) <= Ceil(C, 1/24);

A, B, C

7_266, 10/07/2015 11:30:00 PM, 10/08/2015 7:00:00 AM

];

But I guess the issue is that for 10/07 we are getting 30 minutes. But isn't it suppose to be 30 mins from 11:30 to 12:00 is 30 mins or even to 11:59:59 is 30 mins - 1 second. We want to penalize it by the whole minute? I guess I can force it to be 29 mins, but you have to decide if that is correct.

Best,

Sunny

New Contributor III

## Re: How to find the time difference in minutes with respect to different days ?

Thank you Sunny for the help

MVP

## Re: How to find the time difference in minutes with respect to different days ?

Not a problem. If now you have got all you wanted, I would suggest you to close the thread down by marking the correct response.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

New Contributor III

## Re: How to find the time difference in minutes with respect to different days ?

Yes,sunny i would certainly do that. I had one last query. As you had mentioned,the time period in my scenario is ending at 11:59:59. If i want to showcase it in terms of complete 60 mins instead of  59 mins what changes do i have to do to the existing code to achieve that ?

MVP

## Re: How to find the time difference in minutes with respect to different days ?

You mean in the first case?

MVP

## Re: How to find the time difference in minutes with respect to different days ?

Try this:

Table:

TimeStamp(Round(RangeMax(Temp2, B), 1/86400)) as B,

TimeStamp(Round(RangeMin(Temp, C), 1/86400)) as C,

Interval(Round(RangeMin(Temp, C), 1/86400) - Round(RangeMax(Temp2, B), 1/86400), 'mm') as D;

B,

C,

DayEnd(B + IterNo() - 1) as Temp,

DayStart(B + IterNo() - 1) as Temp2

While DayEnd(B + IterNo() - 1) <= DayEnd(C);