Planning Analytics

 View Only

  • 1.  IBM Planning Analytics (TM1) Filter with IIF and Count MDX

    Posted Mon February 03, 2020 09:29 AM
    Hello Everybody,
    hope you are doing great.

    Suppose I have a cube which is called [Subsidiary Year Mapping] and relates Year dimension and Subsidiary dimension.
    What I want to make a dynamic subset to check if the current year doesn't have any subsidiaries, then return something, otherwise return these subsidiaries.

    Why I need to do that, because if there are not any values returned by the subset, I get an error.

    I attached my code that has an error for your reference.

     
    {Filter({TM1SubsetAll(Subsidiary)}
, 

IIF(
Count(

{Filter({TM1SubsetAll(Subsidiary)}
, [Subsidiary Year Mapping].([Year].CurrentMember, [Subsidiary Year Mapping Measure].[Select]) = "YES")}
) > 0
, 
[Subsidiary Year Mapping].([Year].CurrentMember, [Subsidiary Year Mapping Measure].[Select]) = "YES"

, [Subsidiary].[Code] = "FakeElement"
)

)}


    ------------------------------
    Mohamed Al-Bana
    ------------------------------

    #PlanningAnalyticswithWatson


  • 2.  RE: IBM Planning Analytics (TM1) Filter with IIF and Count MDX

    Posted Tue February 04, 2020 02:18 AM
    Few questions and hopefully I can help a bit.

    Where are you using the subset, what role is it playing. (report, data source for TI, picklist, etc)

    Filters by there nature are conditional why do you need to use an if statement inside your filter statement?

    Based on what you wrote. I think you are trying to get a set that is a list of members if a condition is true, and a single member if that condition is false, correct?

    Cheers




    Original Message


  • 3.  RE: IBM Planning Analytics (TM1) Filter with IIF and Count MDX

    Posted Mon May 11, 2020 11:19 AM
    Mohamed, why don't you resolve the result of the filter just directly in the cube you are using as matrix to decide if there are subsidiaries for the Year? If there are no subsidiaries for the year, turn the alternative 1. You could do that creating a subtotal off the real subsidiaries, if it is zero then "Alternative" will be one. Once that you have that, just apply the single line filter construct that filter the rows according to a title member. Hope this helps you

    ------------------------------
    Jose Gordon
    ------------------------------

    Original Message


  • 4.  RE: IBM Planning Analytics (TM1) Filter with IIF and Count MDX

    Posted Tue May 12, 2020 11:42 AM

    Hi, 

    I believe that you need to create that dynamic subset because you need it to be used in a cube dynamically. Why don't you let the dimension static, and use rule to fill in data on the cell you want:

    ###
    if the current year doesn't have any subsidiaries, then return something, otherwise return these subsidiaries.
    ### ​​

    If the current year doesn't have any subsidiaries, then fill the cell with 0, otherwise do calculation or fill in the cell. 

    if you need the dimension to create another dynamic subset based on the condition you gave, I think it is better you create a matrix like Jode Gordon wrote. 

    Regards,



    ------------------------------
    Veronika Gultom
    ------------------------------

    Original Message