Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Thankyou - works perfectly !