7 Replies Latest reply: May 24, 2013 9:25 AM by Alessandro Bettica

# pls help me for expression - solved

Hi guys, i've a problem on creating a chart table, i need to calculate the intervall between two dates, one on current record and the other one on the previous record for the same code, this is an example of data source :

code     datastart

1          20/01/2013

30        18/01/2013

1          15/01/2013

1          13/01/2013

20        08/01/2013

30        08/01/2013

My chart table shoud be :

Dimension  :code

Expression : avg(datastart-datastart prev*)

datastart prev is the previous value of datastart of previus record for the same code

i.e.(by code)

Code 1 :

avg of 20/01/2013 - 15/01/2013  / 15/01/2013 - 13/01/2013

Code 30 :

avg of 18/01/2013 - 08/01/2013

Code 20 :

avg of 08/01/2013 - 0

I hope is clear.

Thx a lot.

Bye.

• ###### Re: pls help me for expression

See attached qvw

• ###### Re: pls help me for expression

thx Gysbert, it's works very fine, is possible ti have the difference between dates in minutes ?

thx again, bye

Edit :

It seems to be solved by using function time(duration)...is it correct?

• ###### Re: pls help me for expression

Sure, multiply the result by 1440.

• ###### Re: pls help me for expression

Sorry, i want to say a different thing......this is new example :

code

,Date#(datastart,'DD/MM/YYYY') as datastart

,Date#(dataend,'DD/MM/YYYY') as dataend

INLINE [

code,     datastart,          dataend

1,          20/01/2013 15:21:00,     22/01/2013

30,        18/01/2013 15:18:45,    19/01/2013

1,          15/01/2013 15:12:10,     18/01/2013

1,          13/01/2013 15:10:58,     14/01/2013

20,        08/01/2013 15:05:00,     09/01/2013

30,        08/01/2013 15:00:00,     09/01/2013

];

For code n.1, for example, by using your formulas, is it possible to have 00:08:50 as result? the difference between 20/01/2013 15:21:00 - 20/01/2013 15:12:10

• ###### Re: pls help me for expression

Yes, but you'll have to use timestamp# instead of date#.

timestamp#(datastart,'DD/MM/YYYY hh:mm:ss') as datastart.

And use interval(datastart - previous(datastart),'m') to calculate the duration and give them a minutes display format.

• ###### Re: pls help me for expression

it's works fine, post solved.

thx a lot.

bye.

• ###### Re: pls help me for expression

i semplified the data source, i hope is more clear now.

thx.