Query Last element of a dimension that has non zero entry

Canvas Canvas Tips
#1

I need an MDX query to get the last element of a dimension so that the entry in the Cube is non empty.

Imagine the Cube having the following Dimensions:

  1. Instrument (String)
  2. Item Index (Integer in increasing order)
  3. Measure dimension

Now, I want the last Item Index that has non zero entries.
This is what I came up with, but it’s not working:

SELECT { [Measure Dimension].MEMBERS} ON 0 ,  
 Filter( {Item Index] .Members} , [Measure Dimension].[First Measure] <> "" ) ON 1 
	FROM [CubeName]
 Where ( [Instrument].[XYZ9998] )

Thanks for any suggestion.

#2

Hi Roland,

try this for the on 1 :

{ TOPCOUNT ( FILTER ( TM1FilterByLevel ( {TM1SORT( {TM1SUBSETALL( [zSYS Item Index] )}, DESC)} , 0 ) , [Measure Dimension].[First Measure] <> "" ) , 1 ) }

If you combine that with the NON EMPTY statement at the start of the axis definition, it should give you the highest index that has an entry for [First Measure].

Cheers,

Marius

1 Like
#3

@mwirtz Thanks Marius

I changed your suggestion a little bit, using HEAD instead of TOPCOUNT and removing TM1FilterByLevel and it worked fine.

Is there any reason that TM1FilterByLevel should be included? Maybe efficiency reasons?

My new ON 1:

HEAD (
    FILTER({TM1SORT( {TM1SUBSETALL([zSYS Item Index])}, DESC)} , 
        [IR FI Ticker History Measure].[Previous Ticker] <> ''),  1) ON 1
#4

Hi Roland,

I’m glad it works.
I would say HEAD is more suitable than TOPCOUNT.

TM1FilterByLevel just removes the C Elements from the set.
But I believe you don’t have any data on them anyways, so you can also just ignore them.

1 Like