CODEWITHSUNDEEP
mdxiccube
UlrichWuenstel
UlrichWuenstel

Reputation: 470

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

Upvotes: 2

Views: 909

Answers (2)

Marc Polizzi
Marc Polizzi

Reputation: 9375

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.

Upvotes: 3

whytheq
whytheq

Reputation: 35557

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

Upvotes: 0

Related Questions