topic Getting the year from a date variable in QlikView

Getting the year from a date variable

Mon, 21 Sep 2009 23:01:59 GMT

I have an input box containing a variable vStartDate. In the script I want to set another variable vStartYear which is just the year of vStartDate. However I'm having all kinds of trouble achieving what should be a really simple thing! In my script I have the following:

SET vStartYear = Year($(vStartDate));

But if I trace it, it seems to convert it to the string "Year(01/01/2007)". I have tried using the Date function but this also appears as a string. I have also tried various other things, the "best" I've managed is for it to be set to 1899. What am I doing wrong? It's driving me crazy!

Many thanks.

Paul

Getting the year from a date variable

Mon, 21 Sep 2009 23:14:10 GMT

it would seem that the format in which your vStartDate variable is being typed into the input box is not being recognized by QV as a timestamp. For example this works correctly:

Set CurrentDate=Today(2);
Set CurrentYear=Year($(CurrentDate));

Getting the year from a date variable

Mon, 21 Sep 2009 23:19:42 GMT

Try this:

SET vStartYear = Year('$(=vStartDate)');

Dates can sometimes do funny things on the dollar sign expansion. You may not need an equals sign and the single quotes.

Getting the year from a date variable

GabrielAraya — Mon, 21 Sep 2009 23:25:16 GMT

Try the following:

Let vStarYear = Year($(vStarDate))

gabriel

Getting the year from a date variable

disqr_rm — Mon, 21 Sep 2009 23:28:47 GMT

Do you have to use SET? If not, try following. It eliminates all the confusions of dollar sige, etc.

LET vStartYear = Year(vStartDate);

Getting the year from a date variable

Mon, 21 Sep 2009 23:43:49 GMT

Thanks Rakesh, I didn't realise you could use LET and specify variables like that. I'm now getting 1899 as the answer, with my input box containing 01/01/2007. If I add the following line:

LET vStartDate1 = Date(vStartDate,'DD/MM/YYYY')

and reload in debug mode, I can see that vStartDate1 is 30/12/1899. So the problem does appear to be with the input box. How do I get that to recognise that it's a date?

Getting the year from a date variable

prieper — Mon, 21 Sep 2009 23:49:05 GMT

Hi,

you need to change the variables, i.e. LET vStartDate = Date(Year(AnotherDateField), 'DD/MM/YYYY');
Currently you have determined the date-value of an empty variable, vStartDate.

Peter

Getting the year from a date variable

disqr_rm — Mon, 21 Sep 2009 23:54:18 GMT

I don't understand. See enclosed. It's working fine for me. Can you attach a sample here?

Getting the year from a date variable

Tue, 22 Sep 2009 00:00:36 GMT

I think I've fixed it by going to Document Properties, Variables, setting the constraints of vStartDate to Custom and making the custom expression date#. In the debugger, vStartDate now displays as 01/01/07 and vStartYear as 2007. Thanks for putting me on the right track.

Getting the year from a date variable

disqr_rm — Tue, 22 Sep 2009 00:06:43 GMT

Paul, thanks for your feedback. Glad that it's fixed now.