MDX for string to Quarter Conversion Cube Universe

[Former Member]

Hello,

I have a BO Universe in BOXI 3.1 base version built on top of MSAS 2008 cubes.

I have filters in the universe.

Say

IIF([Date].[Quarter].currentmember.properties("CAPTION")>[This Quarter].item(0).lag(4).MEMBER_CAPTION,1, 0) 

You can see that this comparison is essentially between two strings

Now for e.g: If this was a date comparison- i would do the following- convert string to Date using CDate

IIF(CDate([Date].[Date].currentmember.properties("CAPTION"))>CDate([Today].item(0).lag(30).MEMBER_CAPTION,1, 0))

I am wondering what are the equivalent MDX functions i can use for Year, Quarter, Month or Week.

Thanks

[Former Member]

Thanks Didier. Will definitely help in forming expressions.

So in this case, i actually ended up using the KEY instead of the string..and that seems to solve the comparison problem.

AS IIF(([Date].[Quarter].currentmember.properties("KEY"))>([This Quarter].item(0).lag(4).MEMBER_KEY),1, 0) 

[Former Member]

Hi,

Here is the syntax to use for Year:

IIF(YEAR(CDate([Date].[Date].currentmember.properties("CAPTION"))) > YEAR(CDate([Today].item(0).lag(30).MEMBER_CAPTION,1, 0)))

...for Quarter:

IIF(QUARTER(CDate([Date].[Date].currentmember.properties("CAPTION"))) > QUARTER(CDate([Today].item(0).lag(30).MEMBER_CAPTION,1, 0)))

...for Month:

IIF(MONTH(CDate([Date].[Date].currentmember.properties("CAPTION"))) > MONTH(CDate([Today].item(0).lag(30).MEMBER_CAPTION,1, 0)))

You can also concatenate strings like this:

IIF(YEAR(CDate([Date].[Date].currentmember.properties("CAPTION"))) + "/ " + QUARTER(CDate([Date].[Date].currentmember.properties("CAPTION"))) >
YEAR(CDate([Today].item(0).lag(30).MEMBER_CAPTION,1, 0))) + "/" + QUARTER(CDate([Today].item(0).lag(30).MEMBER_CAPTION,1, 0)))

Regards

Didier

[Former Member]

Hi Didier,

Thank you. For some reason Microsoft Analysis Server 2008 does not like these functions QUARTER, YEAR etc

My expression is like

IIF(QUARTER([Date].[Quarter].currentmember.properties("CAPTION"))>QUARTER([This Quarter].item(0).lag(4).MEMBER_CAPTION),1, 0)

I get an error " The '[QUARTER]' function does not exist."

[Former Member]

Hi,

Here are some examples fro retrieving Year, Quarter and Month.

Current Date:

<li>Year

format(Now(), "yyyy")

<li>Quarter

CStr(datepart("q", Now()))

<li>Month

format(Now(), "MM")

Date.Calendar:

<li>Year

format(CDate([Date].[Calendar].currentmember.membervalue), "yyyy")

<li>Quarter

CStr(datepart("q", CDate([Date].[Calendar].currentmember.membervalue)))

<li>Month

format(CDate([Date].[Calendar].currentmember.membervalue), "MM")

Hope this helps

Regards