topic Not recognizing the number 10 as October in QlikView

Not recognizing the number 10 as October

Thu, 17 Nov 2011 22:36:26 GMT

Hello,

I had a variable which i update on a monthly bases for the current month (1 for Jan, 2 for Feb, 10 for Oct)

The only problem is that now with 10 in for the variable all of the expressions using this variable are showing January data. I was told today that qlikview won't recognize the 0 on of the 10, or the second one of 11 or the 2 of 12. Can someone please explain why this is, and also provide an alternative route for this variable?

Thank you very much,

Craig

Re: Not recognizing the number 10 as October

johnw — Thu, 17 Nov 2011 23:20:46 GMT

I have no idea what you're talking about in terms of QlikView thiking that 10 = 1. I'm not denying that you're watching it happen, but I've never seen that happen. I would use real months instead of numeric months (e.g., build with month(Date) as Month). As far as updating the variable, you could set it on the load to the maximum Month for the current year, or just define it as something like this:

=max({1<Year={'$(=max({1} Year))'}>} Month)

Or even this if it makes more sense for your data:

=month(today())

Not recognizing the number 10 as October

Fri, 18 Nov 2011 20:29:26 GMT

I found something that works. I still have no idea why it wouldn't recognize 10, but I use the below function which states 10 as MM and then turns it into 10 and it works. The whole function seems so redundant, but this is the only combination where QV recognized 10 as Oct.

=Date(Date#(10,'MM'),'MM')

It was good advice with the automation of the variable using max. I've run into problems before with the order the date is listed in as to how Max reads the date, but it still works for this. Thanks for the advice.

Craig

Not recognizing the number 10 as October

johnw — Fri, 18 Nov 2011 20:50:28 GMT

So you know what's happening, date#(10,'MM') is telling QlikView to interpret the number 10 as an October date. Since you haven't provided a year or day, it provides a default of 1899 for the year, and 1 as the day. So this is generating the date October 1, 1899. Well, technically, it's generating the numeric equivalent of that date, the number of days since December 30, 1899, which in this case is is -90. The date() function then says to interpret -90 as a date, which is to say as October 1, 1899. However, you then tell it to DISPLAY only the month number of this date, so it displays 10.

If that approach works for you, OK. I've never handled months like that, though.

Not recognizing the number 10 as October

Fri, 18 Nov 2011 21:02:59 GMT

Thank you for this understanding, it makes much more sense now. I gues to really understand this you have to understand how the program processes information.

Much appreciated,

Craig