TM1 Forum

Hi Everyone,
I Just had a lapse of memory and forgot how to grab a date string from a control cube to insert into subset expression.
eg:
{[DateDim].[04/06/2010]:[DateDim].[09/06/2010]}
and replace hard coded date with cell references

Thought it was DB('Control', 'Date1', 'Date Measure') but it must be wrong syntax..


Anyone know what it is?

Thanks

haven't found a solution, but had a few goes..
using record expression 'filter by wildcard', this returns just 1 date:-
{TM1FILTERBYPATTERN( {TM1SUBSETALL( [vDate] )},[Control].([CtlVar].[Start Range],[CtlMeasures].[CtlStr]))}

using t.i. to run a chore to create the subset:-
SubsetCreateByMdx( vSubName, '{[vDate].['| TIMST(pDate, '\d/\m/\Y') |']:[vDate].['| TIMST(vToday, '\d/\m/\Y') |']}' );

I've also had a few tries, the problem is getting mdx to recognise the "join" i.e. the ":" in the middle of the expression, as the two members need to be concatenated together as strings.

for the test;
cube: test
dim1: Rows, elements 1,2,3 etc
dim2: Commentary, elements Commentary (String)
data:
[1],[Cpmmentary] = '1001'
[2],[Commentary] = '2001'

settostr( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [FC_Account] )}, test.([FC_Row].[1],[FC_Commentary].[Commentary]))} )
if you put his into the subset editor then it will return just the first element [1]


settostr({TM1FILTERBYPATTERN( {TM1SUBSETALL( [FC_Account] )}, test.([FC_Row].[2],[FC_Commentary].[Commentary]))})
This returns the second element [2]

so putting them together
strtoset(
settostr( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [FC_Account] )}, test.([FC_Row].[1],[FC_Commentary].[Commentary]))} )
+ ":"+
settostr({TM1FILTERBYPATTERN( {TM1SUBSETALL( [FC_Account] )}, test.([FC_Row].[2],[FC_Commentary].[Commentary]))})
)

should reutrn the converted string expression with the "join" in the middle, however it returns just the first element.

Good Luck
Neil