Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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

Re: pls help me for expression

See attached qvw


talk is cheap, supply exceeds demand
7 Replies

Re: pls help me for expression

See attached qvw


talk is cheap, supply exceeds demand
Not applicable

Re: pls help me for expression

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

thx.

Not applicable

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.


talk is cheap, supply exceeds demand
Not applicable

Re: pls help me for expression

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

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.


talk is cheap, supply exceeds demand
Not applicable

Re: pls help me for expression

it's works fine, post solved.

thx a lot.

bye.

Community Browser