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

[maxgro]

see attachment

[whiteline · Accepted solution]

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)

[#4]

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>

[whiteline]

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().

[#6]

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

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/554851?et=watches.email.thread#554851>

[#7]

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

id

customer

3/2/2014 17:50

created

1

10

5/2/2014 13:04

started

1

10

10/2/2014 11:02

finished

1

10

4/2/2014 10:40

created

2

11

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

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/554851?et=watches.email.thread#554851>

[whiteline]

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.

[#9]

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

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/555904?et=watches.email.thread#555904>

[whiteline]

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