topic Re: pls help me for expression in QlikView

pls help me for expression - solved

Fri, 24 May 2013 10:01:30 GMT

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

Gysbert_Wassenaar — Fri, 24 May 2013 10:14:12 GMT

See attached qvw

Re: pls help me for expression

Fri, 24 May 2013 10:20:36 GMT

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

thx.

Re: pls help me for expression

Fri, 24 May 2013 12:11:20 GMT

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

Gysbert_Wassenaar — Fri, 24 May 2013 12:18:24 GMT

Sure, multiply the result by 1440.

Re: pls help me for expression

Fri, 24 May 2013 12:43:03 GMT

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

Gysbert_Wassenaar — Fri, 24 May 2013 13:00:32 GMT

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

Fri, 24 May 2013 13:25:09 GMT

it's works fine, post solved.

thx a lot.

bye.