MS SQL Server OLAP - Leap Years

Author

Leap Years

Scott Mescall

2006-01-19, 11:23 am

Can anyone tell me a good way to identify a leap year for my time dimension
in a calculated measure. A mod functions would work, but MDX does not
support the mod function. I did an iif() statement similar to this:

iif([Time].[Dimension].[level].[name] = '2004', 364, 365)

I'm looking for an absolute solution, not just looking isolating 2004.

Thanks for the help!

Vladimir Chtepa

2006-01-19, 1:23 pm

Hi,

I would be count the days dimension members, or if it is too expansive, i
would be create property - days_count.

Vladimir Chtepa

"Scott Mescall" < ScottMescall@discuss
ions.microsoft.com> schrieb im
Newsbeitrag news:DC027330-C682-497B-BD93- E7D5573CF79A@microso
ft.com...
> Can anyone tell me a good way to identify a leap year for my time
> dimension
> in a calculated measure. A mod functions would work, but MDX does not
> support the mod function. I did an iif() statement similar to this:
>
> iif([Time].[Dimension].[level].[name] = '2004', 364, 365)
>
> I'm looking for an absolute solution, not just looking isolating 2004.
>
> Thanks for the help!

Deepak Puri

2006-01-19, 8:24 pm

Here's a VBA version - may not be the most efficient:
[color=darkred]
With Member [Measures].[IsLeapYear] as
'Day(DateSerial(CInt
(Ancestor([Time].CurrentMember,
[Time].[Year]).Name), 2, 28) + 1) = 29'

Select {[Measures].[IsLeapYear]} on 0,
[Time].[Month].Members on 1
from Sales[color=darkred]

This is based on the VBA Developer's Handbook Chapter 2: Working with
Dates and Times:

http://msdn.microsoft.com/library/d...y/en-us/dnvbade
v/html/isthisleapyear.asp[color=darkred]
...
Yes, you could write the code to handle this yourself, and it's not all
that difficult. But why do it? VBA is already handling the algorithm
internally. It knows that the day after February 28 (in all but a leap
year) is March 1 but in a leap year it's February 29. To take advantage
of this fact, dhIsLeapYear (shown in Listing 2.16) calculates the answer
for you.
...[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Jimmy

2006-01-30, 1:24 pm

Doesn't a leap year happen every 4 years?

Example against Foodmart 2000:

With Member [Measures].[IsLeapYear] as
'IIF(Int(Time.CurrentMember.Properties("Caption")) / 4 -
Int(Int(Time.CurrentMember.Properties("Caption")) / 4) = 0, 1, 0)'

Select {[IsLeapYear]} on 0, [Time].[Year].Members on 1 from [Sales]

"Scott Mescall" < ScottMescall@discuss
ions.microsoft.com> wrote in message
news:DC027330-C682-497B-BD93- E7D5573CF79A@microso
ft.com...
> Can anyone tell me a good way to identify a leap year for my time
> dimension
> in a calculated measure. A mod functions would work, but MDX does not
> support the mod function. I did an iif() statement similar to this:
>
> iif([Time].[Dimension].[level].[name] = '2004', 364, 365)
>
> I'm looking for an absolute solution, not just looking isolating 2004.
>
> Thanks for the help!

Deepak Puri

2006-01-30, 8:24 pm

That'll work - for a few generations - till 2100!

http://en.wikipedia.org/wiki/Leap_year[color=darkred]
...
The Gregorian calendar, the current standard calendar in most of the
world, adds a 29th day to February in all years evenly divisible by 4,
except for century years (those ending in -00), which receive the extra
day only if they are evenly divisible by 400. Thus 1996 was a leap year
whereas 1999 was not, and 1600, 2000 and 2400 are leap years but 1700,
1800, 1900 and 2100 are not.
...[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Sponsored Links