# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for
Did you mean:
Highlighted
Creator

## Data between two date and time

Hello Community,

I've data of vehicle movement which includes gate in & gate out date and time in different fields and requirement is to generate a report how many for vehicle movement how many in and how many out day wise.In my company the day is calculated as from previous day 6AM to next day 6AM.

Can any one guide how to do it? Sample data attached.

Labels (8)

• ### TimeDifferencebetween2timestamps

1 Solution

Accepted Solutions
Highlighted
Master

## Re: Data between two date and time

Hi Check this revised code and output as desired.

```Data:
Timestamp(GEDATE + GETIME,'DD-MM-YYYY hh:mm:ss') as INTIME,
If(Isnull(Timestamp(GATEOUTDATE + GATEOUTTIME,'DD-MM-YYYY hh:mm:ss')),TimeStamp(Now(),'DD-MM-YYYY hh:mm:ss'),
Timestamp(GATEOUTDATE + GATEOUTTIME,'DD-MM-YYYY hh:mm:ss'))as OUTTIME,
GEDATE, GETIME, GATEENTRYNO, GATENO, GPQTY, GATEOUTDATE, GATEOUTTIME
FROM [Gate In Out.xlsx] (ooxml, embedded labels, table is Sheet1);

Min_Max:
Max(INTIME) as vINMax,
Min(OUTTIME) as vOUTMin,
Max(OUTTIME) as vOUTMax
Resident Data;

Let vINMax = Timestamp(Date(Floor(Peek('vINMax')))+ '06:00:00','DD-MM-YYYY hh:mm:ss');

Let vINMin = Timestamp(Date(Floor(Peek('vINMin')))+ '06:00:00','DD-MM-YYYY hh:mm:ss');

Let vOUTMax = Timestamp(Date(Floor(Peek('vOUTMax')))+ '06:00:00','DD-MM-YYYY hh:mm:ss');

Let vOUTMin = Timestamp(Date(Floor(Peek('vOUTMin')))+ '06:00:00','DD-MM-YYYY hh:mm:ss');

Drop table Min_Max;```

Out put in UI:

HirisH
“Aspire to Inspire before we Expire!”
7 Replies
Highlighted
Specialist

## Re: Data between two date and time

Which count do you want? GPQTY?
Highlighted
Specialist

## Re: Data between two date and time

Try this :

`=INTERVAL(DATE(GATEOUTDATE+ GATEOUTTIME)-DATE(GEDATE + [GETIME]))`

OR if you want it to show with minutes, you can add parameter like :

`=INTERVAL(DATE(GATEOUTDATE+ GATEOUTTIME)-DATE(GEDATE + [GETIME]),'mm')`

- Shubham

Highlighted
Partner

## Re: Data between two date and time

To get the date right you will need to create an master calendar, but your will need to substract six hours from the timestamp when generating your transaction table date key.

Example below:

```Transactions:
floor(Timestamp - interval#(6,'h')) as %date , //skew 6 hours
Timestamp,
Animal
INLINE [
Timestamp,Animal
2019-03-06 01:18:29,Common brushtail possum
2019-03-06 03:55:31,Indian leopard
2019-03-06 05:44:54,Netted rock dragon
2019-03-06 06:22:29,Grey mouse lemur
2019-03-06 07:25:58,Javanese cormorant
2019-03-05 10:59:51,"Sandgrouse, yellow-throated"
2019-03-06 05:31:29,"Reedbuck, bohor"
2019-03-06 06:56:49,,Pied crow
2019-03-06 07:26:09,"Booby, blue-footed"
2019-03-05 00:07:22,Komodo dragon
2019-03-05 07:12:26,Common boubou shrike
2019-03-06 06:55:49,"Siskin, pine"
2019-03-06 23:52:16,Thomson's gazelle
];

Calendar:
num(Date) as %date,
Date,
year(Date) as Year,
Week(Date) as Week,
Weekday(Date) as Weekday
INLINE [
Date
2019-03-06
2019-03-05
2019-03-04
];```
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Master

## Re: Data between two date and time

Hi Check out this below code,

```Data:
If(Floor(INTIME)=Floor(OUTTIME) and Hour(INTIME)<6,'1 D  ' &  Interval(OUTTIME-INTIME,'hh:mm:ss'),
If(Floor(INTIME)<>Floor(OUTTIME) and Hour(INTIME)<6,Floor(OUTTIME)-Floor(INTIME-1)  &' D ' &  Interval(OUTTIME-INTIME,'hh:mm:ss'),
Floor(OUTTIME)-Floor(INTIME)  &' D ' &  Interval(OUTTIME-INTIME,'hh:mm:ss')))
as Interval_Diff;

Timestamp(GEDATE + GETIME,'DD-MM-YYYY hh:mm:ss') as INTIME,
If(Isnull(Timestamp(GATEOUTDATE + GATEOUTTIME,'DD-MM-YYYY hh:mm:ss')),TimeStamp(Now(),'DD-MM-YYYY hh:mm:ss'),
Timestamp(GATEOUTDATE + GATEOUTTIME,'DD-MM-YYYY hh:mm:ss'))as OUTTIME,
GEDATE, GETIME, GATEENTRYNO, GATENO, GPQTY, GATEOUTDATE, GATEOUTTIME
FROM [Gate In Out.xlsx] (ooxml, embedded labels, table is Sheet1);```

From the sample data provided, created above. It will give the Interval in time as well as no of days in between. Based on your below 6 and above 6 logic.

If required as calendar means,let me know all occurrences of this. we can use daystart function for such cases.

Anyway, above doesn't work means. let me know your desired output for all the occurrences.

PFA QVW For ref.

HTH,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
Highlighted
Creator

## Re: Data between two date and time

Hi Subham, I want to count how many vehicles are In and How many vehicles are out. Suppose for this day 19.03.2019 we have to count frim 18.03.2019 morning 6AM to 19.03.2019 6AM.

Thank You for suggestions above !!

Highlighted
Creator

## Re: Data between two date and time

Hi Hirish, I want to count how many vehicles are In and How many vehicles are out. Suppose for this day 19.03.2019 we have to count frim 18.03.2019 morning 6AM to 19.03.2019 6AM.

Highlighted
Master

## Re: Data between two date and time

Hi Check this revised code and output as desired.

```Data:
Timestamp(GEDATE + GETIME,'DD-MM-YYYY hh:mm:ss') as INTIME,
If(Isnull(Timestamp(GATEOUTDATE + GATEOUTTIME,'DD-MM-YYYY hh:mm:ss')),TimeStamp(Now(),'DD-MM-YYYY hh:mm:ss'),
Timestamp(GATEOUTDATE + GATEOUTTIME,'DD-MM-YYYY hh:mm:ss'))as OUTTIME,
GEDATE, GETIME, GATEENTRYNO, GATENO, GPQTY, GATEOUTDATE, GATEOUTTIME
FROM [Gate In Out.xlsx] (ooxml, embedded labels, table is Sheet1);

Min_Max:
Max(INTIME) as vINMax,
Min(OUTTIME) as vOUTMin,
Max(OUTTIME) as vOUTMax
Resident Data;

Let vINMax = Timestamp(Date(Floor(Peek('vINMax')))+ '06:00:00','DD-MM-YYYY hh:mm:ss');

Let vINMin = Timestamp(Date(Floor(Peek('vINMin')))+ '06:00:00','DD-MM-YYYY hh:mm:ss');

Let vOUTMax = Timestamp(Date(Floor(Peek('vOUTMax')))+ '06:00:00','DD-MM-YYYY hh:mm:ss');

Let vOUTMin = Timestamp(Date(Floor(Peek('vOUTMin')))+ '06:00:00','DD-MM-YYYY hh:mm:ss');

Drop table Min_Max;```

Out put in UI:

HirisH
“Aspire to Inspire before we Expire!”