Planning Analytics

Planning Analytics

Get AI-infused integrated business planning

 View Only

  • 1.  MDX Expressions in PAW

    Posted Sun March 31, 2019 11:14 PM
    Good day all,

    I have a MDX issue I have a MDX Filter based on a Date Attribute (yyy-mm-dd)

    Current MDX:
    ORDER(DISTINCT(ORDER(FILTER(TM1FILTERBYLEVEL(TM1SUBSETALL([Product]) , 0) , INSTR(1 , [Product].[Create Date] , '2019-02' , 1) > 0) , [Product].[Create Date] , BASC)) , [Product].[Product].[Category 6] , BDESC)
    View if MDX and Create date Attribute

    This works Quite well, However I would like to make this dynamic ie Link this manually input figure ('2019-02') to a cell In my Control Cube

    Controle Cube View

    Any Ideas on how to do this?
    I have tried Very many options But none have worked when i believe they should have, leading me to believe this is a Syntax Issue

    Im thinking this should work;

    FILTER(TM1FILTERBYLEVEL(TM1SUBSETALL([Product]) , 0) , INSTR(1 , [Product].[Create Date] , '+ [0_Control].([Parameters].[NPD Create Date Filter],[0_Control_m].[String]) +' , 1) > 0)
    but no luck Unfortunately.

    Any assistance with how to write this correctly would be appreciated.

    ------------------------------
    Clifford Muller
    ------------------------------

    #PlanningAnalyticswithWatson


  • 2.  RE: MDX Expressions in PAW

    Posted Mon April 01, 2019 01:58 AM
    Edited by System Admin Fri January 20, 2023 04:22 PM
    Hey Clifford,
    Looks like it is your use of InStr that is causing the issue. 
    https://docs.microsoft.com/en-us/sql/mdx/instr-mdx?view=sql-server-2017. The last argument is not needed and you are trying to see if the string "+ [0_Control].([Parameters].[NPD Create Date Filter],[0_Control_m].[String]) +" is in the value of the attribute. Which is not what you want, you really want to do the test against the results of that query. In MDX the + symbol is concatenate just like | in TI 

    You shouldn't have to use InStr unless you want to do an in-direct comparison. If the value in that cell is always the same value as the attribute you can save some calculation time and just compare directly.

    Here is a simple example:
    {FILTER({TM1SUBSETALL( [Scenario] )}, [Scenario].[Forecast Start Period] = [sys_Config].([sys_config].[Date Test], [sys_config_m].[sValue]))}​



    Cheers!



    ------------------------------
    Ryan Clapp
    ------------------------------

    Original Message


  • 3.  RE: MDX Expressions in PAW

    Posted Mon April 01, 2019 04:09 AM
    Since your date in your cube that you are trying to match is already a string you shouldn't need the ' + +' around your cube data statement.

    ------------------------------
    Robby Meyers
    ------------------------------

    Original Message


  • 4.  RE: MDX Expressions in PAW

    Posted Mon April 01, 2019 12:03 PM
    It should be similar to the direct compare example. If you used instr it would be

    {
  FILTER(
    {TM1SUBSETALL( [Scenario] )},
    InStr(
        [Scenario].[Forecast Start Period], 
        [sys_Config].(
          [sys_config].[Date Test] 
          [sys_config_m].[sValue]
                     )
          )>1
         )
}​


    Instr([dim].[attribute], [cube].([dim1].[elem1]...)) > 1

    Another way is to use rules to parse the values and use a direct compare. Add 2 elements like year compare and month compare, use rules to turn 2019-02 into '2019' and '02' then do the same on the other side. Then you only need to check for '02'='02'

    Finally, you may want to think about storing your dates not as strings but numbers. If you use pure serial dates you can leverage > < operators making more complex filtering easier. Also, serial dates with date formats generate a calendar selector in PAW! One additional note, you'll want to set the tm1s.cfg parameter to force tm1 to use excel dates, that way you can slice and leverage the date features excel has as well.

    ------------------------------
    Ryan Clapp
    ------------------------------

    Original Message