Planning Analytics

 View Only
  • 1.  TM1 Rules for Time

    Posted Mon June 22, 2020 05:19 AM
    Edited by System Fri January 20, 2023 04:51 PM
    Hi GUys

    i have a daily cash flow having day and month grain which i started from Oct 1 2018.
    I have another cube which is month expense allocation which before daily cash flow cube was my entry on the monthly level and the information is populated since Jan 2017.

    i am designing a new solution and on the daily cube i created a load via cube view from my daily cube and it is ok.
    created a second view by month from my allocation cube from jan 2017 to Sep 2018 to populate my new month cube.

    is that possible now going forward via rule add all daily values from my month level (daily cube entry) to my new month cube > Sep 2018 ? i don't want to create each rule for each month.

    Example.
    Daily cube
    Feeders;
    ['Service A','Actual']=>DB('Cube_Monthly Expenses Allocation','Services A',ELPAR('Dim_Date for Daily Cash Flow',!Dim_Date for Daily Cash Flow,1),'Actual');

    Month cube
    Rule
    ['Service A','Actual']=N:DB('Cube_Daily Entry for Cash Flow','Service A',!Time,'Actual');

    Something like if Month >Sep 2018, populate month cube going forward otherwise, no change (mean values from historical cube doesn't change <=Sep 2018.

    Kind Regards

    i have seen some function but as my skill is still under development, i need some guidance.

    There is an Index value for each element and in my case, my daily cube, the month index for Sep 2018 is 17 and for my month cube my index for Sep 2018 is 24. There are two time dimension, ( for daily, day and month as grain) and for month level another time dimension for Month and Year grain.


    Another example on my model and that is the reason for my question.
    Feeders; for daily cube.
    ['Service A','Sep 2018','Actual']=>DB('Cube_Monthly Expenses Allocation','Services A',ELPAR('Dim_Date for Daily Cash Flow',!Dim_Date for Daily Cash Flow,1),'Actual');

    Rules on month cube
    ['Services A','Oct 2018','Actual']=N:DB('Cube_Daily Entry for Cash Flow','Car Services','Oct 2018','Actual');


    ------------------------------
    Kleber Vieira
    ------------------------------
    #PlanningAnalyticswithWatson


  • 2.  RE: TM1 Rules for Time

    Posted Tue June 23, 2020 06:27 AM
    Edited by System Fri January 20, 2023 04:28 PM

    Hi Kleber,

    you can use the If-function for this, combined with the 'continue' function. So something like: 
    If( !Month @> 'Sep 2018', DB('Cube_Daily Entry for Cash Flow','Car Services',!Month,'Actual'), CONTINUE);

    Note: what the 'continue' does, is that it will skip this rule and apply the next rule that applies to this cell. If no such rule exist, so if there's no rule linked to a cell, it will show the original value of the cell, i.e. the value you copied into it with the load process.

    Also, comparing months with names in them will probably give an incorrect result, as they will be sorted alpha-numerically. For example: Oct 2018 will be sorted before Sep 2018, as 'O' comes before 'S'. We usually set up the time dimensions with a numerical-only code as member ID (like 2018-09 or 201809), and put the functional/screen names, like Sep 2018, in aliases. That way, you can compare them more easily. 
    I recommend against using the element index, as those might change to a different element, and member ID's (or aliases) generally stay linked to the same element.

    See also PA documentation on logical rules functions:
    https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_prism_gs.2.0.0.doc/c_logicalrulesfunctions_n8180d.html



    ------------------------------
    Amarins van de Voorde
    ------------------------------



  • 3.  RE: TM1 Rules for Time

    Posted Tue June 23, 2020 10:08 PM
    Edited by System Fri January 20, 2023 04:11 PM
    Thanks for the reply, have tested and nothing happened?

    on the month cube created the following

    ['Rent','Actual']=N:if (!Time @> 'Sep 2018',DB('Cube_Daily Entry for Cash Flow','Rent',!Time,'Actual'), CONTINUE);
    save.

    on the daily do i need to put something along this line too?
    ['Rent','Actual']=>DB('Cube_Monthly Expenses Allocation','Rent',ELPAR('Dim_Date for Daily Cash Flow',!Dim_Date for Daily Cash Flow,1),'Actual');

    sorry, didn't get yet

    ------------------------------
    Kleber Vieira
    ------------------------------



  • 4.  RE: TM1 Rules for Time

    IBM Champion
    Posted Wed June 24, 2020 11:39 AM
    Hi Kleber

    Your condition is looking at the string value for time, so will not act as you expect. you need to change the logic to look for a numeric element in the time dimension. The below example could guide you.

    First you need to add a numeric attribute to your time dimension called 'Number' and  populate the attribute with year|month number. EG: - Sep 2018 = 201809, Oct 2018 = 201810.

    You then need to change the condition from "if (!Time @> 'Sep 2018'," to "if (ATTRN('Time','!Time,'Number') > ATTRN('Time','Sep 2018','Number') ,"

    At a later stage you could add a parameter cube to keep the time element you need to refer to in this rule and swap 'Sep 2018' with the value in the cube.

    Let us know how this change works.


    #PlanningAnalyticswithWatson


  • 5.  RE: TM1 Rules for Time

    IBM Champion
    Posted Wed June 24, 2020 11:39 AM
    Hi Kleber

    You are looking at a condition on a string. This will not do what you want. you need to change your condition to a numeric condition. The below example may help.

    First, you need to add a numeric attribute to the time dimension. lets call it 'number'. populate it with Year|month number. EG sep 2018 will be 201809

    then change your condition above as follows

    ['Rent','Actual']=if (!Time @> 'Sep 2018',DB('Cube_Daily Entry for Cash Flow','Rent',!Time,'Actual'), CONTINUE);

    ['Rent','Actual']=if (ATTRN('Time,!Time,'Number') > ATTRN('Time,'Sep 2018','Number') ,DB('Cube_Daily Entry for Cash Flow','Rent',!Time,'Actual'), CONTINUE);


    As an additional thought, do you use the same 'Time' dimension in both cubes? - if you only need month values in the month expense, you dont need to have the same level of detail in this dimension. i.e, have a separate dimension with months as the lowest level, and not day.



    #PlanningAnalyticswithWatson


  • 6.  RE: TM1 Rules for Time

    Posted Thu June 25, 2020 12:57 AM
    Thanks, will try that


    ------------------------------
    Kleber Vieira
    ------------------------------