Another YTD question (Month numbers in another dimension)

[Varapaavi]

Hi,

So I asked my DW admins to add month names into the DB, and I ended up with another dimension called "Month names" instead of it being in the time dimension as a hierarchy.

It works just fine (Actually great as I can set years on the rows with some measures to make comparisons between years) but I'm having difficulties making my "YTD without current month" measure, as I have no clue how to connect "Current day" to one of the months in the new dimension (its members being numbers 1-12 indicating month number). I figure I could do it by getting the index of the current month within the year level in time dimension and then summing periods to date until that month number.

Any function to get the index number of a member? Or even better, if there exists an easy way out, let me know  ;D

[Varapaavi]

Answering my own question, I don't need to use the recently created dimension for the YTD measure. I will try something along the lines of this method:

https://cognospaul.wordpress.com/2011/06/23/quickie-generate-function-first-n-months-of-years/

Need to figure how to make it fully dynamic, still.

Edit: Got it working, now I'm only eager to get rid of that prompt. My expression is basically identical to one CognosPaul has behind the link:

Code Select Expand


generate
(
   [great_outdoors_company].[Years].[Years].[Year]
   , head
     (
        descendants
        (
           currentMember([great_outdoors_company].[Years].[Years])
           , [great_outdoors_company].[Years].[Years].[Month])
     , #prompt('n','integer')#
     )
)

Any ideas how to extract the current month number as integer, as I have Current date member in my time dimension but the months are in form "2016/Jul"?

[Varapaavi]

I could get the month number in format "08" by following expression:

substring(caption(firstChild([Current Day]));6;2)

[Current Day] being in format "2016-08-09" for this date. However, the report keeps "running" forever if I set it instead of the prompt, and I guess it is because the "08" being probably in string or varchar format? How to get it into value/numberic when I'm using cubes/OLAP/MDX combination?

[bdbits]

If your current date is actually based on today's date, you could use month(current_date) instead. Otherwise, cast(your_expression, integer) should do it.

[Varapaavi]

Thanks for your help. It seems like our DW is having some kind of problem, and I get an error while using either of those functions (sqlPrepareWithOptions -126 or something).

However, I was inspired of another solution which seems to work fine and is actually very neat. I'll post it below in case it could help someone later. You need to define a dynamical data item that picks last month, otherwise basic time hierarchy is sufficient.

Code Select Expand


aggregate(currentMeasure within set
   periodsToDate([Cube].[Date].[Date].[Year] ; cousin([Last month];currentMember([Cube].[Date].[Date])) )
)
Set years on rows, measures nested into them, this data item set to column (or another way around, should work just fine aswell).

I actually tried this earlier but probably mixed the items in cousin() function so it didn't work and I gave up with it...