TM1 Forum

Posted: **Fri May 12, 2023 1:28 pm**

Hi,

I'm trying to write a MDX filter statement to return years from 2018 to upcoming budget year (say 2024 for now).

This one works.

Code: Select all

vIndexDim = 1;
WHILE(TABDIM(vCube, vIndexDim) @<> '');
	vDimName = TABDIM(vCube, vIndexDim);
	SubsetDestroy(vDimName,vSubset);

	IF( vDimName @='Year');
	vMDX= '	{FILTER
			(
			TM1FILTERBYLEVEL (
					      TM1DRILLDOWNMEMBER( {[' | vDimName| '].[(ALL YEARS)]}, ALL, RECURSIVE)
					    ,0
					     )
			,StrToValue ( [' | vDimName| '].currentmember.name )>2017
			 AND
			 StrToValue ( [' | vDimName| '].currentmember.name )<= 2024
			)
		}';
ELSE;
	vMDX = '{TM1FILTERBYLEVEL( TM1SUBSETALL( [' | vDimName | ']), 0)}'; 

               ENDIF;
SubsetCreateByMDX(vSubset, vMDX);

	ViewSubsetAssign(vCube, vView, vDimName, vSubset);
	vIndexDim = vIndexDim +1;
END;

What I want to do is to change 2024 to become a variable, and I added:

Code: Select all

pCurrentBudgetYear = StringToNumber(CellGetS('Control','Current Budget Year', 'svalue'));

What I would like to know is how to change the upper range statement to replace 2024.

Code: Select all

StrToValue ( [' | vDimName| '].currentmember.name )<= 2024

Thanks!
Kenneth

Posted: **Fri May 12, 2023 2:06 pm**

Hi,

You haven't exactly explained what your issue is?

Have you tried adding in pCurrentBudgetYear to your logic, so something like the following:

Code: Select all

StrToValue ( [' | vDimName| '].currentmember.name )<=' | NumberToString( pCurrentBudgetYear )

Maybe you could just reference the control cube in the MDX as follows:

Code: Select all

{FILTER
			(
			TM1FILTERBYLEVEL (
					      TM1DRILLDOWNMEMBER( {[' | vDimName| '].[(ALL YEARS)]}, ALL, RECURSIVE)
					    ,0
					     )
			,VAL ( [' | vDimName| '].currentmember.name )>2017
			 AND
			 VAL ( [' | vDimName| '].currentmember.name )<= VAL( [Control].([Name of dim].[Current Budget Year],[Name of dim].[svalue]) )
			)
		}

I prefer using VAL to STRTOVALUE is situations like this.

Maren

Posted: **Fri May 12, 2023 2:32 pm**

It works already!

My earlier attempt resulted in wrong expression or ambiguity issue with other dimensions.

Thanks a lot!!

TM1 Forum

MDX Filter By Current Member Value Range

MDX Filter By Current Member Value Range

Re: MDX Filter By Current Member Value Range

Re: MDX Filter By Current Member Value Range