Create Previous Month MUN on DMR model

[stan876]

Hi guys,

Long time reader, first time poster, I would like to thank you all for your great help. I found many answers to my problems in the past, unfortunately not to this one, hope you could help.

I built a DMR model using FM on a SQL database.
I have a report developed with RS with a tree prompt based on the Date hierarchy, asking to select a Month.
Right now, I have to select the month manually and get the prompt parameter in a data item (Month_Selected) as is : [Package].[Date].[Date].[Month]->?p_date?

But this report needs to be automated so I need to be able to get this parameter automatically. The parameter needs to be the previous month.

I tried to build the MUN of the previous month :
MUN ex : [Package].[Date].[Date].[Month]->[all].[2016].[201601].[201603]
               Year > Quarter > Month

Now this is what I did :
'[all].['
+
#sq(timestampMask(_add_months($current_timestamp,-1),'yyyy'))#
+ '].[' +
#sq(timestampMask(_add_months($current_timestamp,-1),'yyyy'))# +
CASE
WHEN #sq(timestampMask(_add_months($current_timestamp,-1),'mm'))# BETWEEN 1 AND 3 THEN '01'
WHEN #sq(timestampMask(_add_months($current_timestamp,-1),'mm'))# BETWEEN 4 AND 6 THEN '02'
WHEN #sq(timestampMask(_add_months($current_timestamp,-1),'mm'))# BETWEEN 7 AND 9 THEN '03'
WHEN #sq(timestampMask(_add_months($current_timestamp,-1),'mm'))# BETWEEN 10 AND 12 THEN '04'
END
+ '].[' +
#sq(timestampMask(_add_months($current_timestamp,-1),'yyyy'))# +
#sq(timestampMask(_add_months($current_timestamp,-1),'mm'))#
+']'

It works fine but when I tried to assign this value to the data item Month_Selected it doesn't :(

#'[Package].[Date].[Date].[Month] ->
[all].['
+
#sq(timestampMask(_add_months($current_timestamp,-1),'yyyy'))#
+ '].[' +
#sq(timestampMask(_add_months($current_timestamp,-1),'yyyy'))# +
CASE
WHEN #sq(timestampMask(_add_months($current_timestamp,-1),'mm'))# BETWEEN 1 AND 3 THEN '01'
WHEN #sq(timestampMask(_add_months($current_timestamp,-1),'mm'))# BETWEEN 4 AND 6 THEN '02'
WHEN #sq(timestampMask(_add_months($current_timestamp,-1),'mm'))# BETWEEN 7 AND 9 THEN '03'
WHEN #sq(timestampMask(_add_months($current_timestamp,-1),'mm'))# BETWEEN 10 AND 12 THEN '04'
END
+ '].[' +
#sq(timestampMask(_add_months($current_timestamp,-1),'yyyy'))# +
#sq(timestampMask(_add_months($current_timestamp,-1),'mm'))#
+']'
#

Any idea why ?

If you think about another way to do it (#prompt ?) don't hesitate.

Thanks for your help.
Fabien

[bdbits]

I would probably look head() and tail() functions.

Something like: head(tail(members([Package].[Date].[Date].[Month]), 2))

You might need to flip head() and tail() around, depending on how your time dimension is set up.

[stan876]

Thanks for your help bdbits.
Unfortunately my problem right now is to get automatically the "Previous Month" member, based on the current date.

[bdbits]

I am sorry, I made a rather poor assumption that your time dim had dates up through 'today' but nothing future.

How are you testing "it works fine"? What do you mean by "assign it to a data item" - as in an expression on a data item? Or something else? If you are getting an error message, what is it?

[stan876]

I managed to do it  8)

I created a parameter map to get the quarter based on the month instead of doing a case when (which didn't work) and here is the final calculation :

#'[Package].[Date].[Date].[Month]->[all].['+
timestampMask(_add_months($current_timestamp,-1),'yyyy')
+'].['+
timestampMask(_add_months($current_timestamp,-1),'yyyy')
+
$pm_quarter{timestampMask(_add_months($current_timestamp,-1),'mm')}
+'].['+
timestampMask(_add_months($current_timestamp,-1),'yyyy')
+
timestampMask(_add_months($current_timestamp,-1),'mm')
+']'#

Thanks bdbits for trying to help me out :)