topic Day count in Load Script in QlikView

Day count in Load Script

Thu, 01 Dec 2011 22:14:26 GMT

Hello all,

I'm trying to create a daily annualization based on today's date vs. 365 days a year.

Ideally, I'd like to create in the load script a variable that will auto generate the appropriate factor to multiply today's sales to get me the annualized number.

For example: today is the 334th day of the calendar year (don't know how to get that 334 number)

then use that in the denominator of 365/334 to get the factor to use for today.

Is it possible to get that 334th day somehow? Something along the lines of what is done in excel using the business day function?

Thanks in advance!

Re: Day count in Load Script

johnw — Thu, 01 Dec 2011 23:09:17 GMT

I believe that December 1, 2011 is the 335th day. 31+28+31+30+31+30+31+31+30+31+30+1 = 335, right? Unless you're only counting data through the completion of the previous day, in which case yeah, 334.

I'd have thought this would work:

daynumberofyear(today())

But it returns 336. Checking the documentation, this function wants to pretend that every year is a leap year, I guess so that December 1 has a consistent number whether or not it is a leap year. So perhaps use this:

today() - yearstart(today()) + 1

It returns 335. Skip adding the 1 if you want 334. Mind you, I'm not sure which is really better for annualization. After all, December 31, 2012 is day 366, and do you really want 365/366 as your factor? I'd think you'd want 1 at that point. In any case, either December 31, 2012 has a factor less than 1 instead of 1, or March 1, 2011 has a factor of 365/61 instead of 365/60. Decide which "problem" you prefer, I guess.

Day count in Load Script

Fri, 02 Dec 2011 11:18:12 GMT

you can apply expression as:

=DayNumberOfYear(today(),1)

here today() returns today's date and 1 shows the start month, so you get the answer 337.

Day count in Load Script

Fri, 02 Dec 2011 13:43:55 GMT

Thank you for this! This worked perfectly. I actually also replaced Today with ReloadTime to get the annualization factor since the last refresh of the model only.

Day count in Load Script

Fri, 02 Dec 2011 13:44:13 GMT

Thank you for this as well. Worked great!