Planning Analytics

 View Only
  • 1.  Getting out of the range defined for STET on whole cube

    Posted Wed November 28, 2018 12:51 PM
    This is part of the process of implementing an IRR function in TM1.

    Is there a way to get out of the range defined for a whole cube while using the STET function such as below?

    []=IF(!IRR Iterations @= '1', CONTINUE, STET);
    Rule 1
    Rule 2
    Rule 3

    (Get out of the above range defined such that we can look at the entire cube again and define a different set of rules; after getting out, apply a similar condition again for the next iteration.)

    []=IF(!IRR Iterations @= '2', CONTINUE, STET);
    Rule 4
    Rule 5
    etc.

    I am attempting this in order to apply a repetitive set of IF statements for every metric.
    ​​

    ------------------------------
    Shubho Ghosal
    ------------------------------

    #PlanningAnalyticswithWatson


  • 2.  RE: Getting out of the range defined for STET on whole cube

    Posted Wed November 28, 2018 06:39 PM
    Sorry Shubo, I don't understand your question. Could you try and rephrase it?

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



  • 3.  RE: Getting out of the range defined for STET on whole cube

    Posted Thu December 06, 2018 05:45 PM
    Certainly, Ryan. Below is what I am trying to achieve. This was part of an iterative IRR implementation but it would be useful in other cases as well.

    If (Iteration Number = 1) Then apply the rule set below Otherwise ignore rule set and move on to next condition

    Rule 1
    Rule 2 
    Rule 3

    If (Iteration Number = 2 ) Then rule set below, otherwise move on

    STET really got me excited as it was a way to do the above.

    Unfortunately, once you use it once. I was looking for a way to STET and then GET OUT OF STET such that it can be used multiple times.

    Otherwise, I am looking at repeatedly applying the same set of IF-THEN-ELSE statements over and over.

    Also look at my response to the IRR post for a little more context.

    ------------------------------
    Shubho Ghosal
    ------------------------------



  • 4.  RE: Getting out of the range defined for STET on whole cube

    IBM Champion
    Posted Thu November 29, 2018 02:14 AM
    Have you tried:
    ['IRR Iterations':'1']=<rule>;
    To apply rules to iteration 1 only?

    The problem I see with your:
    []=IF(!IRR Iterations @= '1', CONTINUE, STET);
    and
    []=IF(!IRR Iterations @= '2', CONTINUE, STET);
    Is that where IRR Iterations@='2' in the first statement, it will be STET'ed and not run in the second.
    Difficult to give an exact answer without knowing all the logic.

    ------------------------------
    George
    ------------------------------



  • 5.  RE: Getting out of the range defined for STET on whole cube

    Posted Thu December 06, 2018 05:48 PM
    Hi George,

    You are absolutely right in stating that the second STET would not apply.

    I did use the other suggestion of yours, which is to use a specific Iteration Number in each rule, but it was a little more cumbersome.

    It would have been great to have the following type of way to apply business rules -

    If (Condition) Then (Rule Set Below) Else (Move on to next If)

    Rule 1
    Rule 2
    etc.

    Looks like that is not possible in TM1 at this point, which is unfortunate.

    ------------------------------
    Shubho Ghosal
    ------------------------------



  • 6.  RE: Getting out of the range defined for STET on whole cube

    Posted Fri December 07, 2018 08:29 PM
    What you are asking for is  exactly how rules work in TM1. TM1 rules does not use the ELSE function because if a rule resolves to a CONTINUE then the next applicable rule will be evaluated (Read as the ELSE clause)

    [Apply The Following Rules to These Cells] = CellTypeQualifier:
    IF(Element, Value, or Something Else meets X condition
    , Apply this logic (can be another logical test) if True
    , Apply this logic (can be another logical test) if False
    );

    The key is to remember that the rules are evaluated from top to bottom and a cell can only have 1 calculation attached to it, but it can go through many logical tests to get to the calculation. So for example if you wanted to find something like an effective rate by traversing a location hierarchy it may look like this. 

    ['Effective rate'] =N:
    IF(['rate'] <> 0
      ,['Rate']
      ,IF(DB('Rates by Location', !Version, !Time, ATTRS('location', !Location', 'Region') <> 0
          ,DB('Rates by Location', !Version, !Time, ATTRS('location', !Location', 'Region')
          ,IF(DB('Rates by Location', !Version, !Time, ATTRS('location', !Location', 'Country') <> 0
            ,DB('Rates by Location', !Version, !Time, ATTRS('location', !Location', 'Country')
            ,DB('Rates by Location', !Version, !Time, 'All Locations')
          )
        )
    );

    This rule looks up the tree using attributes to see is there a local rate then we should use it, if not check for the regional rate, then check for the country rate, if all else fails then use the all locations rate.


    You could easily write this as individual statements using CONTINUE as well.

    IF(['rate'] <> 0
      ,['Rate']
      ,CONTINUE
    );

    ['Rate'] =N: F(DB('Rates by Region', !Version, !Time, ATTRS('location', !Location', 'Region') <> 0
       ,DB('Rates by Region', !Version, !Time, ATTRS('location', !Location', 'Region')
       ,CONTINUE
    );
    ..... ETC





















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



  • 7.  RE: Getting out of the range defined for STET on whole cube

    Posted Mon December 17, 2018 12:42 PM
    Hi Ryan,

    Thanks for your response.

    I do understand and appreciate what you have written up.

    My issue was that I cannot run a IF THEN ELSE on its own, without assigning it to an area of the cube.

    This does have the ill effect of having to repeat a IF THEN ELSE statement multiple times, on occasion.

    I was excited about the approach of using CONTINUE and STET in combination that, sort of, gives me that ability.

    [] = IF(Iteration = 1, CONTINUE, STET)

    Until I realized that once you apply STET, you cannot henceforth apply any rule to any other cell other than those identified above using "Iteration=1".

    It would have been nice to be able to apply the above construct multiple times, to different sets of cells.

    The only alternative is to go with ['Metric', '1'] as George indicated in his other post. (In this example, '1' is the same as Iteration = 1.

    It is what it is.

    ------------------------------
    Shubho Ghosal
    ------------------------------



  • 8.  RE: Getting out of the range defined for STET on whole cube

    Posted Tue January 15, 2019 08:27 PM
    In this case if you are going to write the IF(!IRR Iteration = 1) anyway then really you are just trading the IF statement to be the scope definition on the left.

    IE
    [] = IF(!IRR Iteration = 1) just becomes ['IRR Iteration 1'] = 

    In your case it appears you would have to hard code the rules to the iteration count member but it would allow you to write a block of rules for each member of the IRR Iteration dimension.

    In the end you would have needed to repeat the if statements over and over for each iteration anyway so this is really just the same thing but more flexible for the rules definition

    Unless I have missed something from the discussion.

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