pls help me for expression - solved

[#1]

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.

[Gysbert_Wassenaar · Accepted solution]

See attached qvw

[#3]

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

thx.

[#4]

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?

[Gysbert_Wassenaar]

Sure, multiply the result by 1440.

[#6]

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

LOAD

          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

[Gysbert_Wassenaar]

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.

[#8]

it's works fine, post solved.

thx a lot.

bye.