MDX YTD returning null

Question

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.

score 1 · Accepted Answer · 2014-08-07T10:30:14.337

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.

This query was mostly to test the function. My problem is that, the '[Time].[Year - Quarter - Month - Date].CurrentMember' code is not returning the current Time member so the quary doesn't work. — vale, Aug 07 '14 at 14:19
Switch the definition of the YTD_SALES from the Time dimension to the Measures dimension. @Rafal is correct, YTD returns null for the All Member, as it's not a descendant of a specific year and YTD only works for descendants of a Year, of course. However, his change to your query won't work as you have [Time] members on both axis. Change the definition to be [Measures].[YTD_SALES] as YTD( (...), [Measures].[SALES LCY] ), use [Measures].[YTD_SALES] on Columns and remove the WHERE clause. — nsousa, Aug 08 '14 at 08:32

MDX YTD returning null

1 Answers1