cumulate until previous month

[tequila]

hello,

can anyone help me with two  Problems:

1. I want to have report with cumulate result from January (01.2015) to  previous month (05.2015). E.g. Today 17.06.2015 i start the report then I want to have result from 01.01.2015 ->31.05.2015.
I have try with this in the filter (optional) but it don´t work correct. I become the result from 01.01 till today 17.06 and not 31.05.

[FiBu (OLAP)].[Zeit (Ist & Plan)].[Zeit].[Monat].[Monat Num]=#prompt('Monat Num','integer',timestampMask (_add_months($current_timestamp;-1);'mm'))#

2. How can i get simply Date that show previous month on my report (May.2015). I drop the Date from Toolbox but it have only currently month & year

I use OLAP dimensional package

thnx 

[MFGF]

hello,

can anyone help me with two  Problems:

1. I want to have report with cumulate result from January (01.2015) to  previous month (05.2015). E.g. Today 17.06.2015 i start the report then I want to have result from 01.01.2015 ->31.05.2015.
I have try with this in the filter (optional) but it don´t work correct. I become the result from 01.01 till today 17.06 and not 31.05.

[FiBu (OLAP)].[Zeit (Ist & Plan)].[Zeit].[Monat].[Monat Num]=#prompt('Monat Num','integer',timestampMask (_add_months($current_timestamp;-1);'mm'))#

2. How can i get simply Date that show previous month on my report (May.2015). I drop the Date from Toolbox but it have only currently month & year

I use OLAP dimensional package

thnx

Hi,

What is the structure of your date dimension? Do you have Year as a level above month? Can you share with us the MUN (Member Unique Name) of one of your Month members? You can find this by locating a Month member in your package tree, right-clicking on it and selecting "Properties".

You really should not be using a detail filter like this with a dimensional package - it's a much better bet to use an expression in your report to fetch the appropriate members from the cube. If you can share the answers to the questions above with us, we can hopefully help you to build an appropriate expression.

Cheers!

MF.

[tequila]

hi MFGF,

year MUN:
[FiBu (OLAP)].[Zeit (Ist & Plan)].[Zeit].[Jahr].[Jahr - Schlüssel]
Quartal:
[FiBu (OLAP)].[Zeit (Ist & Plan)].[Zeit].[Quartal].[Quartal Num]
Month:
[FiBu (OLAP)].[Zeit (Ist & Plan)].[Zeit].[Monat].[Monat Num]

thnx

[MFGF]

hi MFGF,

year MUN:
[FiBu (OLAP)].[Zeit (Ist & Plan)].[Zeit].[Jahr].[Jahr - Schlüssel]
Quartal:
[FiBu (OLAP)].[Zeit (Ist & Plan)].[Zeit].[Quartal].[Quartal Num]
Month:
[FiBu (OLAP)].[Zeit (Ist & Plan)].[Zeit].[Monat].[Monat Num]

thnx

Hi,

No - you're looking at the wrong things here. We need the Member Unique Name of one of your month members - not of an attribute (as in your attachment). Expand the Mitglieder folder in the Monat level and select one of the members in there - right-click and Properties - then post up the Member Unique Name property.





Cheers!

MF.

[tequila]

Hi MFGF,

ah i have it.

[FiBu (OLAP)].[Zeit (Ist & Plan)].[Zeit].[Monat]->:[RO].[Zeit (Ist & Plan)].[Zeit].[Alle].[2015].[20152].[201505]

[MFGF]

Hi MFGF,

ah i have it.

[FiBu (OLAP)].[Zeit (Ist & Plan)].[Zeit].[Monat]->:[RO].[Zeit (Ist & Plan)].[Zeit].[Alle].[2015].[20152].[201505]

Ok - great! Thanks. Using Nimrod's awesome approach, we can build the MUN of the current month member using the following macro expression:

#'[FiBu (OLAP)].[Zeit (Ist & Plan)].[Zeit].[Monat]->:[RO].[Zeit (Ist & Plan)].[Zeit].[Alle].['+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')+']'#

So - if you drag a query calculation into your crosstab, and use the expression:

periodsToDate([Your Year level goes here], prevMember(#'[FiBu (OLAP)].[Zeit (Ist & Plan)].[Zeit].[Monat]->:[RO].[Zeit (Ist & Plan)].[Zeit].[Alle].['+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 give you a set of month members from the beginning of the year up to and including the previous month.

If you want to get a single cumulative total of your measure(s), you could add the following syntax around this expression:

aggregate(currentMeasure within set <your expression above goes here>)

So - delete your detail filter and use this in a query calculation in your crosstab instead.

Cheers!

MF.