time expression definition doubt

[#1]

Hi guys, could you please help me with this definition?

I have this base table -->

date	status	id
3/2/2014 17:50	created	1
5/2/2014 13:04	started	1
10/2/2014 11:02	finished	1

and what i need is something like this -->

id	amount time in created	amount time in started	amount time in finished	amount total time
1	date(started) - date(created)	date(finished)-date(started)	date(currently)-date(finished)	date(currently)-date(created)

thanks for any help

gerardo

[#2]

Hi Whiteline, here an example:

base table

created date (M/DD/YYYY)	started date (M/DD/YYYY)	finished date (M/DD/YYYY)	id	customer
9/12/2013	9/15/2013	9/16/2013	1	A
8/10/2013	8/11/2013	8/12/2013	2	A
11/3/2013	11/5/2013	11/6/2013	3	A
5/6/2013	5/9/2013	5/10/2013	4	B
5/6/2013	5/9/2013	5/14/2013	5	B
6/8/2013	6/10/2013	6/12/2013	6	C

i have this, thanks to you:

id	amount of days in "created"
1	9/15/2013 - 9/12/2013 = 3 days
2	8/11/2013 - 8/10/2013 = 1 day

What i need is something similar but by customer:

customer	avg amount of days in "created"
A	(3+1+2) / 3 = 2 days avg
B	((5/9/2013 - 5/6/2013) + (5/9/2013 - 5/6/2013) )  / 2 = 3 days avg
C	(6/10/2013 - 6/8/2013) / 1 = 2 days avg

thanks

gerardo

[whiteline]

Ok. The task is to create a table by customer (1) which shows average (3) duration of statuses of each id related to the customer (2).

So that, according to (1) you need customer as chart dimension, (2) you need aggr() function to iterate through each id and calculate duration, (3) you have to use Avg() as aggregation function to combine and shoe the result for each customer.

In terms of expressions it would be:

=Avg(aggr(rangemax(Only({<status={'created'}>}date), Only({<status={'started'}>}date))-Only({<status={'created'}>}date), id, customer))

In your case the customer is not necessary as the ids is unique for each customer.

[#4]

It Works perfectly! Many thanks

Im going to read more a about rangemax and aggr functions…i think they are very usefull…..

De: whiteline

Enviado el: jueves, 26 de junio de 2014 03:26 a.m.

Para: Pauza, Gerardo

Asunto: Re: - time expression definition doubt

Qlik Community<http://community.qlik.com/>

time expression definition doubt

reply from whiteline<http://community.qlik.com/people/whiteline?et=watches.email.thread> in App Development - View the full discussion<http://community.qlik.com/message/556326?et=watches.email.thread#556326>