This Essbase discussion board is provided as a free service and dedicated to all the Essbase professionals out there!
 


  << Previous Topic | Next Topic >>Return to Index  

date criteria in MDX member formula

February 2 2013 at 10:58 PM
No score for this post
Kiran 
from IP address 69.246.214.43

 
Hi
I am trying to write a MDX member formula which has to be date dependent.
So for all 2010 and 2011 dates = formula1,
rest of the dates = formula2.
I am trying to use case statement
case
while ([date].CurrentMember > [2011-12-31])
then
formula2
else
formula1
end

this does not work since the search_condition is not valid.

I can use UDAs to tag 2010 and 2011 dates with UDA1 and other dates with UDA2 and use isUda in case statement. But I would like to know if I can avoid using UDAs and achieve this with MDX formula.

Please advise

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
AuthorReply
Anonymous

69.246.214.43

urgent.. please help - mdx: case statement with dates as search_condition

No score for this post
February 11 2013, 9:03 AM 

Hi guys

I am trying to write a MDX member formula which has to be date dependent.
So for all 2010 and 2011 dates = formula1,
rest of the dates = formula2.
I am trying to use case statement
case
while ([date].CurrentMember > [2011-12-31])
then
formula2
else
formula1
end

this does not work since the search_condition is not valid.

I can use UDAs to tag 2010 and 2011 dates with UDA1 and other dates with UDA2 and use isUda in case statement.

is there any approach to achieve the above mdx formula.

thanks in advance


Kiran M

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
GlennS

72.254.242.57

Re: date criteria in MDX member formula

No score for this post
February 11 2013, 11:36 AM 

What do your dimesions look like, especially for how you deal with time?

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Kiran

69.246.214.43

Re: date criteria in MDX member formula

No score for this post
February 12 2013, 12:57 AM 

Hi Glenn

The Dates dimensions is tagged as None, and the members are in 2011-01-31, 2011-02-28 format.

I am trying to use this date member as criteria for case statement.
so

case
when ([Date].CurrentMember) >2011
then
formula1
else
formula2
end

I also tried using DatePart function as
when DatePart ([Date].CurrentMember, DP_YEAR)>2011 but I got an error during retrieval as "invalid date specification in function [DatePart]"

Please suggest

Thanks

Kiran M

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Jennifer H

66.77.129.3

Re: date criteria in MDX member formula

No score for this post
February 11 2013, 12:07 PM 

Try this:

Case
When DatePart ([Date].CurrentMember, DP_YEAR) > 2011
Then formula2
Else formula1
End

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Kiran

69.246.214.43

Re: date criteria in MDX member formula

No score for this post
February 12 2013, 12:49 AM 

Hi Jennifer

Thanks for the quick response.
I tried to use DatePart in the member formula.
It didnt throw me any syntax error but during retrieval I got error "invalid date specification in function [DatePart]"

My date members are in format 2011-01-31, 2011-02-28 so on..

Shud the date members be in any specific format? Also shud the dimension be tagged as any specific dimension type?


thanks,

Kiran M

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
PD

159.53.110.140

Substring

No score for this post
February 12 2013, 8:21 AM 

Looks like MDX has a substring function, could you use that to get the first four characters (if the first four are always year)?

I have no idea what impact on performance that would have

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
GlennS

63.127.232.196

Re: Substring

No score for this post
February 12 2013, 9:38 AM 

Since these are member names and I assume you have parents that denote the years, you could use the ISancestor function for the currentmember. Note ISAncestor does both ancestors and descendants dependent on the order of members so something like
Case When ISANCESTOR(time.currentmember,time.[FY10]) Then
....
When...
End

Note you might need to switch the two paramaters, I never remember which goes first

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Kiran

69.246.214.43

Re: Substring

No score for this post
February 20 2013, 9:35 AM 

Thanks Glenn

My date dimension is in the format below with ~ operator. Its a flat dimension with month ends. So all members are level 0 members.

Date_dimension
|-> 2011-01-31
|-> 2011-02-28
|-> 2011-03-31
and so on.

I have used Substring as well as DatePart functions in mdx member formulae.
- Substring([Date].CurrentMember,1,4) - failed during formulae verification
- DatePart([Date].CurrentMember,DP_YEAR) - verified successfully but during retrieval threw error "Invalid date spedified in function [DatePart]"

Please help.

Thanks

Kiran M

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Kiran

69.246.214.43

Re: Substring

No score for this post
February 20 2013, 9:40 AM 

Hi

The substring function failed during formulae validation.

I tried substring function as below in the member formulae

Substring([Date].CurrentMember,1,4) - failed with error "Sysntax error at token Substring"

Please help


Kiran M

 
Scoring disabled. You must be logged in to score posts.Respond to this message   

Gary Crisci

205.228.53.11

Re: Substring

No score for this post
February 20 2013, 11:24 AM 

There are a number of ways to do this given the simple layout of your time dim, if it were me, I would just set a member range since you know the starting and ending members of the range.

Datepart is not going to work because you do not have dates, you have strings that just happen to look like dates.

To answer your specific question you would use the following

CASE
WHEN SUBSTRING([Date].CurrentMember.MEMBER_NAME, 1,4) = ("2011") THEN 1
WHEN SUBSTRING([Date].CurrentMember.MEMBER_NAME, 1,4) = ("2012") THEN 1
ELSE 0 END'

Note that CurrentMember returns a member, the substring function is expecting a string, so you need to add the property MEMBER_NAME to get the member as a string.


To use a member range you could do something like

CASE
WHEN Contains([Date].CurrentMember,
MemberRange([Date].[2012-12-31], [Date].[2012-12-31].FirstSibling))
THEN 1
ELSE 0 END

You could then set the [2012-12-31] as a subvar on the server and update it when the year changes without having to touch the outline.

Couple of other ways to do this but either of these should be sufficient.

Good luck




 
Scoring disabled. You must be logged in to score posts.Respond to this message   
kiran

208.54.36.153

Re: Substring

No score for this post
February 26 2013, 9:14 AM 

thanks for the detailed explanation Gary.
I tried both options and they worked.
I wish tech ref is more detailed.

thanks
Kiran

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
 
  << Previous Topic | Next Topic >>Return to Index  
 Copyright © 1999-2014 Network54. All rights reserved.   Terms of Use   Privacy Statement  

RSS feed for this forum - http://www.network54.com/Forum/58296?xml=rss. Please email hypess (at) gmail.com, if you have any questions/feedback/issues.