August 17, 2011 at 11:37 pm
Hi friends i have some doughts please somebody help
in sql we have getdate() to get current date/today date wat is the function in MDX?
i had ccountry ,state,city hierarchy and population is measure...how can we get top 5 countries with top 5 states using the measure population
August 18, 2011 at 10:54 am
See if this helps
August 19, 2011 at 6:38 am
The last solution is interesting, though as I recall I had trouble using the Now() function successfully, at least for what I intended.
If you use an autonumber type key for your date dimension, you could consider something like this:
VBA!DateDiff("d", '1/1/06', VBA![Date]())
I call this "YesterdaysDateID", and it is an invisible calculation in my cubes. The date represents Day 1 of the data warehouse. The difference will point to the correct ID for yesterday. If I were to make a today's ID, I would change the date in this case from 1/1/06 to 12/31/05. Here is an example of a formula I use it in:
LINREGPOINT(
RANK([Schedule On Date].[Date].CurrentMember,
LastPeriods(28, StrToMember('[Schedule On Date].[Date].&[' + VBA!Cstr([Measures].[YesterdaysDateID]) + ']'))),
LastPeriods(28, StrToMember('[Schedule On Date].[Date].&['+ VBA!Cstr(VBA!Cstr([Measures].[YesterdaysDateID])) + ']')),
[Measures].[Scheduled Time Avg (D)],
RANK([Schedule On Date].[Date].CurrentMember,
LastPeriods(28, StrToMember('[Schedule On Date].[Date].&[' + VBA!Cstr(VBA!Cstr([Measures].[YesterdaysDateID])) + ']'))))
This formula computes the linear regression on 28 days worth of date for one of the date dimensions.
Some people swear by having readable dates as the key for their date dimensions. My view is that it would make many calculations much harder. It might even make this one impossible.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply