topic Re: Adding variable date field to an expression in QlikView

Adding variable date field to an expression

danielnevitt — Fri, 07 Mar 2014 11:00:01 GMT

Hi,

I have the following Expression and I would like to replace the hard coded date with a variable vToday.

vToday = Date(Today()) - This is already set as a variable

FABS(SUM( IF(TRADE_TYPE='Swap' AND BS_IND='Fixed' AND INTERNAL_IND='-' AND FINAL_PRICING_DT>='06/03/2014', PAYMENT_QTY*(-PRICE1) , IF(TRADE_TYPE='Swap' AND BS_IND='Float A' AND INTERNAL_IND='-' AND FINAL_PRICING_DT>='06/03/2014',PAYMENT_QTY*PRICE2 ,0 ) )))

Any help with this will be much appreciated.

Regards,

Daniel

Re: Adding variable date field to an expression

tresB — Fri, 07 Mar 2014 11:02:40 GMT

Replace your '06/03/2014' with $(vToday)

Re: Adding variable date field to an expression

danielnevitt — Fri, 07 Mar 2014 11:06:02 GMT

Hi,

Thanks for the reply.

Unfortunately that suggestion doesn't work. Am I missing some () further on in the expression?

Regards,

Daniel

Re: Adding variable date field to an expression

rustyfishbones — Fri, 07 Mar 2014 11:12:03 GMT

Can you share the App?

Re: Adding variable date field to an expression

Fri, 07 Mar 2014 11:15:49 GMT

Daniel,

1) Check the formats of the different dates: I think they must be the same for QV to work properly (that is the case in set analysis)

2) If the format is the same, try to use num() especially for your variable vToday = num(today())

Fabrice

Re: Adding variable date field to an expression

MK_QSL — Fri, 07 Mar 2014 11:31:32 GMT

The Format of FINAL_PRICING_DT and vToday should be same.

Consider that FINAL_PRICING_DT is 'DD-MM-YYYY' and vToday = 'DD/MM/YYYY'

Do as below...

FINAL_PRICING_DT = {'$(=Date(Date#(vToday,'DD/MM/YYYY'),'DD-MM-YYYY'))'}

Re: Adding variable date field to an expression

fernando_tonial — Fri, 07 Mar 2014 11:49:14 GMT

Hi,

Please, replace your '06/03/2014' with '$(vToday)'

Set a Variable in script:

SET vToday = Date(Today(),'DD/MM/YYYY');

Best Regards.

Tonial.

Re: Adding variable date field to an expression

Fri, 07 Mar 2014 11:58:47 GMT

Hi Danel,

Are u sure the field FINAL_PRICING_DT values are same as the values in defined variable ,

If those are fine ..it works ,You didn't miss anything if u do that..

Re: Adding variable date field to an expression

Fri, 07 Mar 2014 17:48:55 GMT

Any news of all that ?

vToday = num(today()) is certainly the best choice because the variables are not dual (all fields in QV are dual like date, integers but variables are not). By storing an integer with the num() function, you force the > operator to use the integer part of the date.

Fabrice