topic Re: time expression definition doubt in QlikView

time expression definition doubt

Mon, 23 Jun 2014 15:53:19 GMT

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

Re: time expression definition doubt

maxgro — Mon, 23 Jun 2014 18:43:15 GMT

see attachment

Re: time expression definition doubt

whiteline — Mon, 23 Jun 2014 18:47:26 GMT

Hi.

Assuming that you always have three statuses you can use set analysis like this:

=Only({<status={'started'}>}date)-Only({<status={'created'}>}date)

More likely 'created' event can occur without 'started' and 'finished'. This case you can handle like this for example:

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

Re: time expression definition doubt

Mon, 23 Jun 2014 19:29:49 GMT

Hi Whiteline, thank you for your answer.

The expression works perfectly, unfortunately i forgot that some times one ID could have 2 dates in “started” status. So…..Is it possible to take only the last date value of each status?

Thanks

gerardo

De: whiteline

Enviado el: lunes, 23 de junio de 2014 03:48 p.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/554327?et=watches.email.thread#554327>

Re: time expression definition doubt

whiteline — Tue, 24 Jun 2014 12:21:42 GMT

Hi.

So that you should decide which one to use for calculations and use the appropriate aggregation function.

I guess the Min() for 'started' status is resonable instead of Only().

Re: time expression definition doubt

Tue, 24 Jun 2014 12:27:02 GMT

Perfect! Thank you very much!

De: whiteline

Enviado el: martes, 24 de junio de 2014 09:22 a.m.

Para: Pauza, Gerardo

Asunto: Re: - time expression definition doubt

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

time expression definition doubt

Re: time expression definition doubt

Wed, 25 Jun 2014 13:32:52 GMT

Hi Whiteline, could you please help me to add the aggr function in the expression?

To be more clear, in the same example i added a new top level: “customer”

date

status

customer

3/2/2014 17:50

created

5/2/2014 13:04

started

10/2/2014 11:02

finished

4/2/2014 10:40

created

In this example we need to calculate the amount of time in “created” status per customer and not only by id.

So…i tried to use aggr function but qlikview cant resolve the expression, i need always the id in the graph to a well resolve of the expression.

Thanks

gerardo

De: Pauza, Gerardo

Enviado el: martes, 24 de junio de 2014 09:27 a.m.

Para: 'jive-1844794110-34v1-2-bw4j@qliktech-public.hosted.jivesoftware.com'

Asunto: RE: - time expression definition doubt

Perfect! Thank you very much!

De: whiteline

Enviado el: martes, 24 de junio de 2014 09:22 a.m.

Para: Pauza, Gerardo

Asunto: Re: - time expression definition doubt

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

time expression definition doubt

Re: time expression definition doubt

whiteline — Wed, 25 Jun 2014 13:42:29 GMT

Hi.

You have distinct ids here for dofferent customers, so that it doesn't matter.

Otherwise you should deside what means the instance that have status. It could be something represented by 'id' or by combination of 'id' and 'customer id' or just by 'customer id' or something else.

Aggr function is not the case. It would be useful if you want for example iterate through all the events relative to customer, calculate the durations and then show one Sum/Avg/Mean/etc value per customer. In other words you need aggr when you want to calculate something for the level that differs from chart dimensions.

Re: time expression definition doubt

Wed, 25 Jun 2014 14:01:04 GMT

Thanks for your answer Whiteline.

you right, doesn’t matter because i have differents “id” but the problem is that i need the id in the graph to resolve the expression. If i only keep in the graph customers qlikvew can’t resolve it.

Thanks

gerardo

De: whiteline

Enviado el: miércoles, 25 de junio de 2014 10:43 a.m.

Para: Pauza, Gerardo

Asunto: Re: - time expression definition doubt

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

time expression definition doubt

Re: time expression definition doubt

whiteline — Wed, 25 Jun 2014 16:17:57 GMT

Gerardo, could you add a simple sample and describe desiered behaviour ?

Re: Re: time expression definition doubt

Wed, 25 Jun 2014 20:39:36 GMT

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

Re: time expression definition doubt

whiteline — Thu, 26 Jun 2014 06:25:27 GMT

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.

Re: time expression definition doubt

Thu, 26 Jun 2014 13:32:29 GMT

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