Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Day count in Load Script

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!

1 Solution

Accepted Solutions
MVP
MVP

Re: Day count in Load Script

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.

4 Replies
MVP
MVP

Re: Day count in Load Script

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.

Not applicable

Day count in Load Script

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.

Not applicable

Day count in Load Script

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.

Not applicable

Day count in Load Script

Thank you for this as well.  Worked great!

Community Browser