Comapare dates in set analysis

[Anonymous]

Hi

I am trying to use set analysis to get the difference from two dates in an set analysis

My case is that I have many routes on any given date.

I need to compare routes ready on floor agianst loading to truck.

The table would look something like

Route	Date	To_Floor	To_ramp	To_Truck
123	2013.04.12	2013.04.12 09:00:00	2013.04.12 09:07:00	2013.04.12 09:12:00
123	2013.05.12	2013.04.12 08:31:00	2013.04.12 08:40:00	2013.04.12 08:45:00
125	2013.04.12	2013.04.12 09:10:00	2013.04.12 09:14:00	2013.04.12 09:21:00
127	2013.04.12	2013.04.12 07:00:00	2013.04.12 07:03:00	2013.04.12 07:23:00
123	2013.06.12	2013.04.12 19:00:00	2013.04.12 19:05:00	2013.04.12 19:19:00

My goal is to build a table where I compare the time between to_floor ->to_ramp and  to_ramp ->to_truck

Giving something like

Route	Date	Floortime	Loadtime	Totalleadtime
123	2013.04.12	07:00	05:00	12:00
123	2013.05.12	09:00	05:00	14:00
125	2013.04.12	04:00	07:00	11:00

My initial idea was:

sum(

          {$<

               Category = {'to_ramp'}

           >}

          Date

)

-

sum(

          {$<

               Category = {'to_floor'}

          >}

          Date

)

But we can have situatiions where one route runs two or more times a day. The formua above would then give the sum for that day. I need to be able to evauate each route.

Any comments?

BR

DJ

[#2]

Use:

Expression between floor and ramp

=Interval(Toflor-ToRamp, 'mm') //gives you difference in minutes - you could also yse 'hh:mm'

Expression between ramp and truck

=Interval(ToRamp-ToTruck, 'mm')

Define 3 variables:

let ToFlor = num(timestamp#('YOURCOULMN','YYYY/MM/DD  hh:mm:ss TT'));

let ToRamp= num(timestamp#('YOURCOULMN','YYYY/MM/DD  hh:mm:ss TT'));

let ToTruck= num(timestamp#('YOURCOULMN','YYYY/MM/DD  hh:mm:ss TT'));