Question
· Jan 4, 2021

Why does NOW member not work properly in MDX Query Tool?

In the MDX Query Tool I can't seem to get the NOW relative syntax to work correctly.  If I just use ".&[NOW]" it works, but something like ".&[NOW-1]" or ".&[NOW-1d]" (for dates extracted using DayMonthYear) it throws an error.

So this works:

 

But if I change .&[NOW] to .&[NOW-1] I get this cryptic error message.

 

Also, can you use member functions off of NOW?  For example can you do something like: [TransactionDateFilter].[H1].[Date].&[NOW].PREVMEMBER ???

Product version: IRIS 2020.1
Discussion (8)1
Log in or sign up to continue

I was able to work around the issue by using LAG functions.  However there is one very strange caveat.  Any attempt to run a query with NOW in it that has a function off of the NOW function (e.g. ".&[NOW].LAG(1)") will fail with the same cryptic error as attempting the relative NOW functions such as ".&[NOW-1]". 

However, if you first run the query with hardcoded values instead of NOW, it will execute.  THEN if you change the hard coded value to "NOW" and execute it, forever forward, it will work.  It's inexplicable but I got it to work that way.  So with the below example, I would get an error until I ran the query with the commented out hardcoding first, then that did something (perhaps in the cube cache???) that now allows me to run the query without error.

Hello Lee, The error you are seeing here is actually coming from failed evaluation of the CURRENTMEMBER, which is probably having trouble finding the correct context imposed by the NOW-x in the background.

What version was this in?

As mentioned previously, there was a known issue in the handoff to background processing that has been corrected which might be the solution to your situation. I would recommend filing this with the WRC in order to investigate your options in more detail. Generically speaking, everything you are quoting regarding the NOW syntax specifically should be supported, including references with or without the ampersand and the use of LAG/LEAD/PREVMEMBER/etc functions hung off the NOW references.

Hi Dan,

I think this is two issues.  One may be a bug with the inconsistent NOW behavior.  The other issue may be user error.  I did notice that if I was careful to make sure there was a member that should be referenced by NOW, I could clear up the error in those cases.  For example if the query is referencing "yesterday" but I had not updated my dev cube in 24 hrs (and had no yesterday data) it would error.  The error would go away if I updated my dataset and cube to have a yesterday.  One of the key takeaways is to understand what is "populated" into the IRIS data dimensions.  The date dimensions are more auto-pilot than other MDX systems, which is really nice and makes creating cubes fast.