topic Really irritating custom date issue ! in QlikView

Really irritating custom date issue !

Mon, 19 Dec 2011 09:33:16 GMT

HI,

I have a really annoying issue ...

I have a custom calendar in excel which I load i.e. (uk format)

MyDate Custom Week

05/12/2011 45

06/12/2011 45

What I would like is to create / set a value for a variable that gives me the custom week number based on today's date i.e

MyVariable=if(MyDate=today(2),Custom Week)

I tried using an on open trigger to do the above after the data has loaded but it just doesn't like the format.

Does anyone have any ideas please ?

Thanks

Bob

swuehl — Mon, 19 Dec 2011 11:34:42 GMT

Bob,

I assume that MyDate is not correctly recognized as Date type.

Either set globally the date format using

SET DateFormat='DD/MM/YYYY';

at the top of your script, or if you only want to parse in that field with that format, use date#() function:

MyDate:

LOAD

Date#(MyDate,'DD/MM/YYYY') as MyDate,

[Custom Week]

FROM YourExcel.xls

Then you should be able to call the correct week in any chart object using:

=only(if( MyDate = today(2), [Custom Week]))

Hope this helps,

Stefan

Mon, 19 Dec 2011 11:47:46 GMT

Thankyou - works perfectly !