IcCube - Get yesterday's Member in MDX Query

2
votes

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...

2
mdxiccube
check LookupByKey function that is a better alternative – ic3

2 Answers

3
votes

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.

0
votes

Is the -1 causing the error of NOW?

So maybe the following, which deleted the -1 and adds a single LAG, would work?

STRTOMEMBER("[date].[date].[day].["+ VBAMDX.FORMAT(VBAMDX.NOW(),"yyyy-MM-dd") +"]").LAG(1)

I just noticed that you're missing, in your code, an ampersand in front of the square bracket of the last section of the member name, so please try one of the following:

STRTOMEMBER("[date].[date].[day].&["+ FORMAT(NOW()-1,"yyyy-MM-dd") +"]")

or

STRTOMEMBER("[date].[date].[day].&["+ VBAMDX.FORMAT(VBAMDX.NOW()-1,"yyyy-MM-dd") +"]")

If you try these 3 measures do they both fail?

WITH
  MEMBER [Measures].[Today1] AS 
    VBAMDX.NOW() 
  MEMBER [Measures].[Today2] AS 
    VBAMDX.NOW()-1
  MEMBER [Measures].[Today3] AS 
    VBAMDX.FORMAT(VBAMDX.NOW()-1,"yyyy-MM-dd")
...