CODEWITHSUNDEEP
nullssasmdx
vale
vale

Reputation: 65

MDX YTD returning null

I'm new to MDX and i was trying to use the YTD function on my cube but I'm getting a problem with it.

If I specify the date in the code it works fine:

    WITH MEMBER [Time].[Year -  Quarter -  Month -  Date].[YTD_SALES] AS
    Aggregate(
        YTD([Time].[Year -  Quarter -  Month -  Date].[August 2014])
    )
SELECT 
    [Time].[Year -  Quarter -  Month -  Date].[YTD_SALES] ON COLUMNS,
    [Item].[Item].Children ON ROWS
FROM
    [TBA_SALES]
WHERE
    [Measures].[Sales LCY]

but if I replace [March 2014] with CurrentMember the results come back all null.

Appreciate any help.

Upvotes: 1

Views: 1507

Answers (1)

user170442
user170442

Reputation:

Do you have your Time dimension set as time type?

Another thing what you can try is to see what are you getting as current member. To try this add:

MEMBER YTDStr AS MEMBERTOSTR([Time].[Year -  Quarter -  Month -  Date].CurrentMember)

then add it to query as you would do for measure:

...
SELECT 
    { [Time].[Year -  Quarter -  Month -  Date].[YTD_SALES], YTDStr } ON COLUMNS,
...

EDIT: You are getting [Time].[Year - Quarter - Month - Date].[All] which is top member in hierarchy. Therefore it is not possible to return YTD for all member.

In order to fix this issue you need to add a time dimension member on rows:

  WITH MEMBER [Time].[Year -  Quarter -  Month -  Date].[YTD_SALES] AS
    Aggregate(
        YTD([Time].[Year -  Quarter -  Month -  Date].[August 2014])
    )
SELECT 
    [Time].[Year -  Quarter -  Month -  Date].[YTD_SALES] ON COLUMNS,
    {[Item].[Item].Children, [Time].[Year -  Quarter -  Month -  Date].ALLMEMBERS} ON ROWS
FROM
    [TBA_SALES]
WHERE
    [Measures].[Sales LCY]

I am not sure what you are after in this query, so it's kind of difficult to point you in right direction.

Upvotes: 1

Related Questions