Search code examples
mdxiccube

IcCube - Get yesterday's Member in MDX Query

I have a Date Dimension looking like this [date].[date].[day].[yyyy-MM-dd] and I would like to get the member representing yesterday i.e. [date].[date].[day].[2016-07-27]. How can I accomplish this.

In some threads about MDX, but not IcCube specific, it is written like this:
1. STRTOMEMBER("[date].[date].[day].["+ FORMAT(NOW()-1,"yyyy-MM-dd") +"]")
2. STRTOMEMBER("[date].[date].[day].["+ VBAMDX.FORMAT(VBAMDX.NOW()-1,"yyyy-MM-dd") +"]")

The first solution throws an error stating 'unknown function FORMAT' and the error in the second solution reads '"VBAMDX.NOW()" is expecting a single parameter "index"'. Same goes for VBAMDX.FORMAT if I replace VBAMDX.NOW with only NOW. But all pages explaining VBAMDX have two parameters for FORMAT and none for NOW...

Solution

  • I guess this is because Excel FORMAT is not supported (error reporting has been improved in the latest version of icCube). Instead I would use the functions as mentionned in this page:

    StrToMember( "[Time].[Calendar].[Day].&["+ DateToString( NOW()-1,"yyyy-MM-dd") +"]")

    But in icCube you've LookupByKey function that is a more robust solution if your key is a date :

    LookupByKey( [Time].[Calendar].[Day],  Now() )

    or if you need a date :

    LookupByKey( [Time].[Calendar].[Day],  Today() )

    You can easily navigate dates with functions (doc).

    Hope that helps.