question

swags avatar image
swags asked

Retrieving and storing static values in MDX (added code snippet)

I'm rewriting this, as it was worded very poorly. Here's the code I have to select the last 30 days from today: LastPeriods(30,StrToMember("[Calendar].[Period].&[" + CSTR(YEAR(Now()) * 10000 + Month(Now()) * 100 + Day(Now())) + "]")) In my Calendar dimension, I have a column called "LastSalesDate", which I mark with an 'X' for the date which we have final sales info. Currently, it is marked for 9/30/2012. Basically, I need the part that says "Now()" to be changed to the date that corresponds to the "LastSalesDate" ='X' value. So, it will pull the last 30 days as of 9/30/2012, instead of today. Hopefully this makes more sense. ------ I’m having issues retrieving and storing a static date value. The value that I need is either gotten as the max date from the sales data, or a one-record table which stores the first and last sales date. I am using it to help calculate last 30/90/180 days sales. I cannot use today’s date to subtract from, because our data is always several weeks behind. I am very new to MDX (as this question probably shows) and have hit a wall trying to retrieve either of these values. I've tried a Calculated Member, but the best I've been able to return (from the one-record table) is "All". Once I have this date, I can pass it into the Named Set for the 30/90/180 day filter. What is the best way to get at this information? Thanks for the help!
mdx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
There are several possibiities how to achieve this. One of the possibilities is creating a static named set, which will contain your LastSalesDate You can create it inside the Cube Script and it will look something like. CREATE SET CURRENTCUBE.[Last Sales Date] AS Filter([Calendar].[Period].MEMBERS, [Calendar].[Period].CURRENTMEMBER.Properties(LastSalesDate) = "X"), DISPLAY_FOLDER = 'Named Sets'; Then you can reference Item(0) of the Set. LastPeriods(30, [Last Sales Date].Item(0))
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

swags avatar image swags commented ·
Thanks, Pavel! Taught me a few things just in those few lines. I tried it, but was getting an error saying "The dimension attribute was not found". It turns out, I just needed to put quotes around "Last Purchase Date" in the Properties part. (note: putting brackets around it will not work). Updated below to avoid confusion: CREATE SET CURRENTCUBE.[Last Sales Date] AS Filter([Calendar].[Period].MEMBERS, [Calendar].[Period].CURRENTMEMBER.Properties("LastSalesDate") = "X"), DISPLAY_FOLDER = 'Named Sets' Thanks for the help, this is now doing exactly as intended!
0 Likes 0 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
It depends, how you have your `[Date]` dimension designed. For this to work, you need the Attribute `[LastSalesDate]` to be part of the dimension and to be directly related to the `[Period]` Attribute, so it can be used as Period's property. Anyway, as you have correctly noted, the it should put the `LastSalesDate` into double quotes in the `Properties` function `...CURRENTMEMBER.Properties(`"`LastSalesDate`"`)=`"`X`"`)...`
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.