topic Re: expression definition with dates in QlikView

expression definition with dates

Tue, 15 Apr 2014 19:53:48 GMT

Hi Community, im trying to resolve the following expression.

I have this table

product	op	time_start	time_end
A	122	8:30	17:30
B	123	9:00	11:00
C	124	11:20	13:50
D	125	13:40	14:10

what i need is the PROM value (in hours) between all time_end and time_start, columns, in this case:

A --> 9 hours

B --> 2 hours

C --> 2,5 hours

D --> 0,5 hours

PROM: 3,5 hours

thanks

gerardo

Re: expression definition with dates

Tue, 15 Apr 2014 21:33:02 GMT

I have example for you.

I hope that it helps.

regards

Darek

Re: expression definition with dates

its_anandrjs — Tue, 15 Apr 2014 21:57:46 GMT

Take

Dimension:- Product

Expression:- time(time_start-time_end)

Re: expression definition with dates

Wed, 16 Apr 2014 14:44:53 GMT

Sorry Dariusz, i couldn't open the file.

Could you please write the expression here?

thanks

Re: expression definition with dates

Wed, 16 Apr 2014 14:47:06 GMT

script:

LOAD product,

op,

time_start,

time_end

FROM

(ooxml, embedded labels, table is data);

dimension: product

expression:

Time( sum(time_end-time_start))

Re: expression definition with dates

Wed, 16 Apr 2014 14:47:47 GMT

Hi Anand, the problem is that i need to select at least one OP value. If i select only a Product the value is null.

thanks

Re: expression definition with dates

Wed, 16 Apr 2014 14:51:16 GMT

Thanks for your answer Dariusz, the problem is that i need to include the dimension Product in the expression, if not some times the value is NULL. For example when no values (products or op) are select.

is it clear?

thanks

Re: expression definition with dates

Wed, 16 Apr 2014 14:56:31 GMT

This is why you should always share your sample data with those kind of questions....

Re: expression definition with dates

Wed, 16 Apr 2014 15:12:44 GMT

if you remove the dimension product in your example, this expression works?

Re: Re: expression definition with dates

Wed, 16 Apr 2014 15:34:18 GMT

now it works

Re: Re: expression definition with dates

Wed, 16 Apr 2014 17:17:44 GMT

sorry...i can not open the file...i have a personal edition versión...

Re: Re: expression definition with dates

its_anandrjs — Wed, 16 Apr 2014 18:12:47 GMT

After load of your table then in straight table

Dimension :- Product

Expression :- Time((time_end-time_start),'hh:mm' )

it will show

Dimension :- Product

Dimension :- op

Expression :- Time((time_end-time_start),'hh:mm' )

and it will show

I believe it will show correct values let me know.

Re: Re: expression definition with dates

Wed, 16 Apr 2014 18:26:41 GMT

i forgot. but i hope than you like qv more and more and at the end you will have license

Now:

script:

the same as earlier

dimension: product or nothing or as you want

expression:

Time(avg(aggr( sum(time_end-time_start),product)))

Hope it helps.

regards

Darek

Re: expression definition with dates

Wed, 16 Apr 2014 18:37:28 GMT

Sure! Thanks!

De: Dariusz Mielczarek

Enviado el: miércoles, 16 de abril de 2014 03:27 p.m.

Para: Pauza, Gerardo

Asunto: Re: - Re: expression definition with dates

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

Re: expression definition with dates

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

Re: expression definition with dates

Wed, 16 Apr 2014 18:46:58 GMT

Remember to let me know if it was helpful and if it solves your problem

regards

Darek

Re: expression definition with dates

Wed, 16 Apr 2014 19:06:52 GMT

Sorry but not…let me send you the .qvw file

In this example I need the average value -4,45 (you can see it in the straight table) in the average box.

Many thanks

gerardo

De: Dariusz Mielczarek

Enviado el: miércoles, 16 de abril de 2014 03:47 p.m.

Para: Pauza, Gerardo

Asunto: Re: - expression definition with dates

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

expression definition with dates

Re: expression definition with dates

Wed, 16 Apr 2014 19:48:24 GMT

But you asked about time in your first post. I can see, that you have dates in your application.

This expression:

avg(aggr( sum(AR_OF_LUXEVENT_AD.USR_PDRMVH_FCHMOV-AR_OF_LUXEVENT_AD.USR_PDRMVH_FCHENT),%Key_NUMERO_OF_AR))

gives result as yours, i think (and hope )

Re: expression definition with dates

Thu, 17 Apr 2014 15:14:10 GMT

Is not working….qlikview cannot resolve the expression….

De: Dariusz Mielczarek

Enviado el: miércoles, 16 de abril de 2014 04:49 p.m.

Para: Pauza, Gerardo

Asunto: Re: - expression definition with dates

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

expression definition with dates

Re: expression definition with dates

Thu, 17 Apr 2014 17:49:14 GMT

I copied this expression stright from your qvd exaple. Try again to use this expression in example you shared (Tablero formulas .....)

avg(aggr( sum(AR_OF_LUXEVENT_AD.USR_PDRMVH_FCHMOV-AR_OF_LUXEVENT_AD.USR_PDRMVH_FCHENT),%Key_NUMERO_OF_AR))