If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Calculating Current Month on Time Dimension

Started by phantomluvs, 04 Sep 2015 12:27:27 AM

Previous topic - Next topic

phantomluvs

Seeking help from all Multidimensional reporting experts

We are using TM1 as our OLAP data source for reporting. The time dimension created in TM1 doesn't have Current month / current period item that can be used in report studio.

Below is the MNU of a month:
My MNU: [Monthly Income Statement].[t_time].[t_time].[Year]->:[TM].[t_time].[t_time].[@MEMBER]. [Jun '15]
I need help with calculating current month data item in report studio. I have tried many methods
•   Find Current Period using closingPeriod Function
o   closingPeriod ([sales_and_marketing_cs].[Time].[Time].[Month])
•   Find Current Period by Filtering on Measure Data
o   item(tail(filter(members([sales_and_marketing].[Time].[Time].[Month]),  tuple([Revenue], currentMember([sales_and_marketing].[Time].[Time]))  is not null), 1), 0)
Also tried constructing [Jun '15] using #sb(timestampMask($current_timestamp,'mm'))#)), which doesn't work

MFGF

Quote from: phantomluvs on 04 Sep 2015 12:27:27 AM
Seeking help from all Multidimensional reporting experts

We are using TM1 as our OLAP data source for reporting. The time dimension created in TM1 doesn't have Current month / current period item that can be used in report studio.

Below is the MNU of a month:
My MNU: [Monthly Income Statement].[t_time].[t_time].[Year]->:[TM].[t_time].[t_time].[@MEMBER]. [Jun '15]
I need help with calculating current month data item in report studio. I have tried many methods
•   Find Current Period using closingPeriod Function
o   closingPeriod ([sales_and_marketing_cs].[Time].[Time].[Month])
•   Find Current Period by Filtering on Measure Data
o   item(tail(filter(members([sales_and_marketing].[Time].[Time].[Month]),  tuple([Revenue], currentMember([sales_and_marketing].[Time].[Time]))  is not null), 1), 0)
Also tried constructing [Jun '15] using #sb(timestampMask($current_timestamp,'mm'))#)), which doesn't work

Hi,

You could try the following to build the MUN:

#'[Monthly Income Statement].[t_time].[t_time].[Year]->:[TM].[t_time].[t_time].[@MEMBER].[' + substitute('''','',substitute('''','', substitute(timestampMask($current_timestamp,'mm'),'',csv(grep (timestampMask($current_timestamp,'mm'),array('01Jan','02Feb','03Mar','04Apr','05May','06Jun','07Jul','08Aug','09Sep','10Oct','11Nov','12Dec'))))))+' '+substitute('20','''',timestampMask($current_timestamp,'yyyy'))+']'#

Incidentally, the MUN you posted above contains a space between the [@MEMBER]. and [Jun '15] parts - I assume this is a typo?

The expression I posted above will always return the MUN of the month member that corresponds with the system date (eg today is 4th September 2015 so it will return the last part as [Sep '15])

Cheers!

MF.
Meep!

phantomluvs

You are Awesome Muppet, it worked like a charm.
Yes the space is a typo.