[ANSWERD]How to transfrom current date to time dimension member in periodsToDate

[erwink]

Hi everybody

My user would like to have a column with the total of a given measure from starting Year up to current month

I have a DRM model with a time hierarchy Year.Quarter.Month

I tested my formula with dragging a member ==> total ([RequestedHours] within set periodsToDate ([Year],[March 2016]))

Now I would like to replace the member with "Current-Month" member. But all my tries failed
One  was to generate the MUM  as #'[Dimensions].[BYTime].[BYTimeH].[Month]->[ALL].[2016].[20161].[' + timestampMask($current_timestamp,'yyyymm') +']'#

Is there a way to extend the Dimension BYTime with CurrentMonth, or to generate the Member.

Thank you
erwin

[MFGF]

Hi everybody

My user would like to have a column with the total of a given measure from starting Year up to current month

I have a DRM model with a time hierarchy Year.Quarter.Month

I tested my formula with dragging a member ==> total ([RequestedHours] within set periodsToDate ([Year],[March 2016]))

Now I would like to replace the member with "Current-Month" member. But all my tries failed
One  was to generate the MUM  as #'[Dimensions].[BYTime].[BYTimeH].[Month]->[ALL].[2016].[20161].[' + timestampMask($current_timestamp,'yyyymm') +']'#

Is there a way to extend the Dimension BYTime with CurrentMonth, or to generate the Member.

Thank you
erwin

Hi,

I'd suggest either you try using the closingPeriod() function to deliver the latest (last) month member from your model

closing Period([your Month level])

or else if you have months in the dimension that are in the future, we explore the macro option a little further and get it working. To see what we need, you need to post up a complete MUN of a month member so we can see the structure (hint - right-click on a Month member in the package tree and choose Prpoerties, then copy the Member Unique Name from there).

Cheers!

MF.

[erwink]

Hi MF
Thank you fro your prompt reply as usual

Unfortunately I've values in the future. The report has planned values per month for one year and actuals up to current month
My user would like to be able to compare plan value up to current month with the actuals

A month MUM is as follow [Dimensions].[BYTime].[BYTimeH].[Month]->[all].[2016].[20161].[201603]

So to be correct I should replace all three with whatever formula

Thank you so far
erwin

[MFGF]

Hi MF
Thank you fro your prompt reply as usual

Unfortunately I've values in the future. The report has planned values per month for one year and actuals up to current month
My user would like to be able to compare plan value up to current month with the actuals

A month MUM is as follow [Dimensions].[BYTime].[BYTimeH].[Month]->[all].[2016].[20161].[201603]

So to be correct I should replace all three with whatever formula

Thank you so far
erwin

Ah! In that case, we need to thank the hugely innovative Nimrod Avissar for his input to the following macro code:

#'[Dimensions].[BYTime].[BYTimeH].[Month]->[all].['+timestampMask ($current_timestamp,'yyyy')+'].['+timestampMask ($current_timestamp,'yyyy')+substitute('''','',substitute('''-','',substitute(timestampMask ($current_timestamp,'mm'),'',csv(grep(timestampMask ($current_timestamp,'mm'),array('01-1','02-1','03-1','04-2','05-2','06-2','07-3','08-3','09-3','10-4','11-4','12-4'))))))+'].['+timestampMask ($current_timestamp,'yyyymm')+']'#

This should deliver a MUN in the format you posted.

Cheers!

MF.

[erwink]

Hi MF

Crazy macro...but unfortunately fails on my report

Parsing error before or near position: 514 of: "member(total ([RequestedHours] within set periodsToDate ([Year],#'[Dimensions].[BYTime].[BYTimeH].[Month]->[all].['+timestampMask ($current_timestamp,'yyyy')+'].['+timestampMask($current_timestamp,'yyyy')+substitute('''','',substitute('''-','',substitute(timestampMask ($current_timestamp,'mm'),'',csv(grep(timestampMask ($current_timestamp,'mm'),array('01-1','02-1','03-1','04-2','05-2','06-2','07-3','08-3','09-3','10-4','11-4','12-4'))))))+'].['+timestampMask ($current_timestamp,'yyyymm')+']'#)),'','ReqToDate"

I choose first Data Item Type: Calculated Measure and 2nd as Data Item with ended with a slickly different error

Parsing error before or near position: 493 of: "(total ([RequestedHours] within set periodsToDate ([Year],#'[Dimensions].[BYTime].[BYTimeH].[Month]->[all].['+timestampMask ($current_timestamp,'yyyy')+'].['+timestampMask($current_timestamp,'yyyy')+substitute('''','',substitute('''-','',substitute(timestampMask ($current_timestamp,'mm'),'',csv(grep(timestampMask ($current_timestamp,'mm'),array('01-1','02-1','03-1','04-2','05-2','06-2','07-3','08-3','09-3','10-4','11-4','12-4'))))))+'].['+timestampMask ($current_timestamp,'yyyymm')+']'#))"


I understand the macro and do not see a flaw or typo.
But what type of item shall i use. Shall I calculate the member in a separate item and use it than in the periodsToDate calculation.

I feel still newbie and definitively do not know what to do. Thank you for any hint

[erwink]

It works!!!

Than you MF thank you Nimrod Avissar

The issue was that the last ' was transformed to ` during the copy of the whole string

:) :) :)

[MFGF]

It works!!!

Than you MF thank you Nimrod Avissar

The issue was that the last ' was transformed to ` during the copy of the whole string

:) :) :)

Outstanding! Glad it works for you! :)

MF.

[erwink]

Hi MF

Sorry to bother you again. One last question / issue on this super macro

How should it look if instead of $current_timesatmp I'd like to use maximum([Dimensions].[Plan-Actual_MS].[ActualCaptureDate]) with ActualCaptureDate be a datetime

I tried
1) replace it => failed
2) replace it with cast (maximum([Dimensions].[Plan-Actual_MS].[ActualCaptureDate]) , char(10)) =>failed
3) created a new DataItem with the cast, and use it in the macro => failed

thank you
erwin

[MFGF]

Hi MF

Sorry to bother you again. One last question / issue on this super macro

How should it look if instead of $current_timesatmp I'd like to use maximum([Dimensions].[Plan-Actual_MS].[ActualCaptureDate]) with ActualCaptureDate be a datetime

I tried
1) replace it => failed
2) replace it with cast (maximum([Dimensions].[Plan-Actual_MS].[ActualCaptureDate]) , char(10)) =>failed
3) created a new DataItem with the cast, and use it in the macro => failed

thank you
erwin

Hi,

If you're using a macro. you can't use this approach. Macros are evaluated and resolved before the query is executed, so you can't use any value/member/tuple/measure from your data source inside a macro.

Cheers!

MF.

[erwink]

ok, and how would you do that

SAP data are unfortunately not ready at the same date each month.. therefore my need

[MFGF]

ok, and how would you do that

SAP data are unfortunately not ready at the same date each month.. therefore my need

In that case you're looking at using the closingPeriod() function (which you said you can't use). Is the ActualCaptureDate item a member? Could you use the closingPeriod() function to retrieve the latest one, then use the linkMember() function to retrieve the corresponding member from your time dimension?

MF.

[erwink]

Hi MF
I'm glad you take time to help me out. I don't know other way to learn all this stuff. I did DM and RS studio training from IBM...but those are more or less straight forward.

I've plan data from start BY up to 18 month in the future. And actual data from start BY to e.g April

I would like to sum plan data up to April so the user can compare Plan/Actual.

ActualCaptureDate is a attribute as date time.

I I understand you correctly, your proposal would be to modify the ActualCaptureDate column in the datamodel to be a member of my BYTime dimension.
An then use closingPeriod([Dimensions].[Plan-Actual_MS].[ActualCaptureDate]) to get the member.
Could I then use it in periodsToDate ?

like total ([RequestedHours] within set periodsToDate ([Year],closingPeriod([Dimensions].[Plan-Actual_MS].[ActualCaptureDate]) )

Thank you
erwin