Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

See attached qvw


talk is cheap, supply exceeds demand

View solution in original post

7 Replies
Gysbert_Wassenaar

See attached qvw


talk is cheap, supply exceeds demand
Not applicable
Author

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

thx.

Not applicable
Author

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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.


talk is cheap, supply exceeds demand
Not applicable
Author

it's works fine, post solved.

thx a lot.

bye.