MDX: Filtering on negative values

[20 Ton Squirrel]

I'm trying to make a view using MDX that will show all negative values in a cube. I'm quite new to MDX and I'm failing badly at this so far. I'm hoping some kind soul can point me in the right direction.

I'm using Wim's guide to learn, check it here. The example provided for filtering on cube values is below:

Code: Select all

{ Filter ( TM1FilterByLevel ( TM1SubsetAll( [PL_Account] ) , 0 ) 
          , [BalanceSheet].(  [FIN_Year].[2021] 
                            , [FIN_Period].[P07] 
                            , [FIN_Scenario].[Actual] 
                            , [BS_Measures].[Amount] ) <> 0 ) 
}

On my end, the cube is called Operator Model and has these dimensions:

Code: Select all

Version, Country, Month, Operator, Platform, Resolution, Adjustment, Operator Model Measures

Here's my stab at the MDX which is, of course, absolutely wrong. I attempted to put the filter in the first part of the statement, the column area.

Code: Select all

SELECT 

NON EMPTY 
    { FILTER ( 
              TM1FILTERBYPATTERN ( { TM1FILTERBYLEVEL ( { TM1SUBSETALL ( [Month] ) } , 0 ) }
                                  , "Dec ????" ) 
              , [Operator Model].( [Version].[Current]
                                  ,[Operator Model Measures].[Digital Subscribers]
                                 ) < 0 ) }
    ON 0, 
    
NON EMPTY 
      { TM1SubsetToSet ( [Country].[Country]   , "All Country N Elements"  , "public" ) } 
    * { TM1SubsetToSet ( [Operator].[Operator] , "All Operator N Elements" , "public" ) } 
    * { TM1SubsetToSet ( [Platform].[Platform] , "All Platform N Elements" , "public" ) }
    ON 1 

FROM [Operator Model] 

WHERE (  [Adjustment].[Adjustment].[Final]
       , [Resolution].[Resolution].[Total Resolution]
       , [Version].[Version].[Actual]
       , [Operator Model Measures].[Operator Model Measures].[Digital Subscribers]
      )

The MDX editor won't tell me where it is wrong, it just barfs. Any ideas?

[20 Ton Squirrel]

I figured out part of it tinkering around in Arc's MDX tool.

Part of the issue was in the filter evaluation statement. While you are not required to specify ALL dimensions from the cube, it might be necessary to be specific so the evaluation knows what to look at. Wim even mentioned this in his blog.

BAD

Code: Select all

{ FILTER ( 
              TM1FILTERBYPATTERN ( { TM1FILTERBYLEVEL ( { TM1SUBSETALL ( [Month] ) } , 0 ) }
                                  , "Dec ????" ) 
              , [Operator Model].( [Version].[Current]
                                  ,[Operator Model Measures].[Digital Subscribers]
                                 ) < 0 ) }

BETTER

Code: Select all

    { FILTER ( 
              TM1FILTERBYPATTERN ( { TM1FILTERBYLEVEL ( { TM1SUBSETALL ( [Month] ) } , 0 ) }
                                  , "Dec ????" ) 
              , [Operator Model].(  [Version].[Actual]
                                  , [Country].[World]
                                  , [Month].CurrentMember
                                  , [Operator].[All Operators]
                                  , [Platform].[All Platforms]
                                  , [Resolution].[Total Resolution]
                                  , [Adjustment].[Original]
                                  , [Operator Model Measures].[Digital Subscribers]
                                 ) < 0 ) }

There is still a problem, however. Since I'm looking for a negative value in a "view" that includes so many consolidations (world, all operators, all platforms)... there potentially won't be any negatives.

BEST BUT NOT GREAT

Code: Select all

    { FILTER ( 
              TM1FILTERBYPATTERN ( { TM1FILTERBYLEVEL ( { TM1SUBSETALL ( [Month] ) } , 0 ) }
                                  , "Dec ????" ) 
              , [Operator Model].(  [Version].[Actual]
                                  , [Country].[World]
                                  , [Month].CurrentMember
                                  , [Operator].[GoTV]
                                  , [Platform].[All Platforms]
                                  , [Resolution].[Total Resolution]
                                  , [Adjustment].[Original]
                                  , [Operator Model Measures].[Digital Subscribers]
                                 ) < 0 ) }

If I specify an actual operator (GoTV) in my dimension list, that narrows the scope of the view down enough to produce results.

This isn't a good solution, though, since I need to find any negative values anywhere in the cube. I can't evaluate this over consolidations.

Seems like my best bet might be to make a process that cycles through and flags cells with "error codes" instead of using a broad-stroked MDX query.

Any thoughts are welcome on THAT particular idea.

[declanr]

By the looks of the first MDX view you got to so far:

• You have multiple December months as columns
• All leaf elements from Country, Operator and Platform on your rows
• 4 other dimensions as Title Elements

You have tried placing the negative filter in the Month columns.
If you only want to show negatives the layout will be a problem, imagine that on your first row Dec 2020 has a negative but all other Decembers are positive, based on that your filter gives you Dec 2020.
The second row however, has Dec 2021 as a negative and all other months as positive. This causes it to keep Dec 2021.

Based on those 2 rows you now have the months Dec 2020 and Dec 2021. Due to the nature of a cube, it will also show you the 2 positive values that I think you want excluded.

I *think* if you want to only show negative values, you would also need to move your months onto the rows. And add one of your title elements as a singular column header instead.

After that instead of applying the filter on a single dimension, you apply it to the entire tuple that is on your rows.

Code: Select all

SELECT 

NON EMPTY 
    {[Version].[Version].[Actual]}
    ON 0, 
    
    FILTER ( 
            { TM1FILTERBYPATTERN ( { TM1FILTERBYLEVEL ( { TM1SUBSETALL ( [Month] ) } , 0 ) }, "Dec ????" ) }
            * { TM1SubsetToSet ( [Country].[Country]   , "All Country N Elements"  , "public" ) } 
            * { TM1SubsetToSet ( [Operator].[Operator] , "All Operator N Elements" , "public" ) } 
            * { TM1SubsetToSet ( [Platform].[Platform] , "All Platform N Elements" , "public" ) }
	,
        [Version].[Version].[Actual] < 0
    )
    ON 1 
FROM [Operator Model] 
WHERE (  [Adjustment].[Adjustment].[Final]
       , [Resolution].[Resolution].[Total Resolution]
       , [Operator Model Measures].[Operator Model Measures].[Digital Subscribers]
      )

[declanr]

I should also add that if you stick it into the View MDX box in PAW it will convert it a bit to have a distinct function in something like below:

Code: Select all

SELECT 

NON EMPTY 
    {[Version].[Version].[Actual]}
    ON 0, 
    
    {FILTER ( 
    	{DISTINCT (
            { TM1FILTERBYPATTERN ( { TM1FILTERBYLEVEL ( { TM1SUBSETALL ( [Month] ) } , 0 ) }, "Dec ????" ) }
            * { TM1SubsetToSet ( [Country].[Country]   , "All Country N Elements"  , "public" ) } 
            * { TM1SubsetToSet ( [Operator].[Operator] , "All Operator N Elements" , "public" ) } 
            * { TM1SubsetToSet ( [Platform].[Platform] , "All Platform N Elements" , "public" ) }
         )}
	,
        [Version].[Version].[Actual] < 0
    )
    ON 1 
FROM [Operator Model] 
WHERE (  [Adjustment].[Adjustment].[Final]
       , [Resolution].[Resolution].[Total Resolution]
       , [Operator Model Measures].[Operator Model Measures].[Digital Subscribers]
      )

If you haven't played with the view MDX box in PAW I would actually recommend it, similar to the MDX box like we've always had for subsets in architect its not super helpful in telling you where your mistake is but when the MDX you write if correct enough its interesting seeing how it converts it to something slightly different e.g. adding distinct as above or swapping a CROSSJOIN for the * symbol etc.

[20 Ton Squirrel]

THANK YOU, Declan, I really appreciate your advice. I'll give this a try and see how it pans out.

Having views like this will be very important for the analysts on this project, I need to have some quick checkpoints for them to go in and see where errors lie.

[20 Ton Squirrel]

GOT IT.

Code: Select all

SELECT 
  NON EMPTY 
   {[Operator Model Measures].[Operator Model Measures].[Digital Subscribers]} 
  ON COLUMNS , 
  NON EMPTY 
   FILTER (  {TM1SubsetToSet([Month].[Month], "December Months")} 
           * {TM1SubsetToSet([Operator].[Operator], "All Operator N Elements")}  
           , [Operator Model Measures].[Operator Model Measures].[Digital Subscribers] < 0 ) 
  ON ROWS 
FROM [Operator Model] 
WHERE 
  (
   [Version].[Version].[Actual],
   [Adjustment].[Adjustment].[Original],
   [Resolution].[Resolution].[Total Resolution],
   [Country].[Country].[World],
   [Platform].[Platform].[All Platforms]
  )

You were absolutely right, Declan. I needed to shuffle the fields around and filter on the rows.

My brain is wired for SQL, it is going to take some time to get accustomed to this MDX stuff.