date criteria in MDX member formula

date criteria in MDX member formula

Joined: January 15th, 2013, 8:08 pm

February 3rd, 2013, 3:58 am #1

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
Kiran M
Quote
Like
Share

Joined: January 15th, 2013, 8:08 pm

February 11th, 2013, 2:03 pm #2

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
Kiran M
Quote
Like
Share

GlennS
GlennS

February 11th, 2013, 4:36 pm #3

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
What do your dimesions look like, especially for how you deal with time?
Quote
Share

Joined: January 25th, 2013, 7:28 pm

February 11th, 2013, 5:07 pm #4

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
Try this:

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

Joined: January 15th, 2013, 8:08 pm

February 12th, 2013, 5:49 am #5

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
Kiran M
Quote
Like
Share

Joined: January 15th, 2013, 8:08 pm

February 12th, 2013, 5:57 am #6

What do your dimesions look like, especially for how you deal with time?
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
Kiran M
Quote
Like
Share

PD
PD

February 12th, 2013, 1:21 pm #7

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
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
Quote
Share

GlennS
GlennS

February 12th, 2013, 2:38 pm #8

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
Quote
Share

Joined: January 15th, 2013, 8:08 pm

February 20th, 2013, 2:35 pm #9

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
Kiran M
Quote
Like
Share

Joined: January 15th, 2013, 8:08 pm

February 20th, 2013, 2:40 pm #10

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
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
Kiran M
Quote
Like
Share


Confirmation of reply: