Palo last element of parent's previous element

    Palo last element of parent's previous element

    Hello everybody,

    I'm excited by this software as well as with it's excel implementation. I am now into creating different rules and I need an assistance with something.

    In attachment, you can see, I successfully created a rule that returns "forward" from previous "Period" + adds "MTD" because it points on month-to-date data.

    A structure, that you can also see in attachment, is Year - Months.

    If user chooses let's say "2012/Mar" period, rule returns forward for "2012/Feb MTD" period. Problem begins if user choose 2012/Jan. It this case, I want rule to return forward for 2011/Dec.

    Can you help me with this issue?

    rule code is:

    Source Code

    1. ['Begining Balance'] = PALO.DATA("europart_data","Accounting",!'Accounts',!'Organizations',!'Years',!'Months',CONCATENATE(PALO.EPREV("europart_data","Periods",!'Periods')," MTD"),"Forward")
    Images
    • question_palo.png

      118.01 kB, 1,366×768, viewed 354 times
    • periods.png

      13.47 kB, 316×526, viewed 409 times
    Although this type of Time design I have never seen - You have Years, Months & Periods - Why have you done this ?
    Have you considered using View dimension instead of Period having 2 elements MTD & YTD ? it should be a simpler design.

    I think this is what you need :
    ['Begining Balance'] = PALO.DATA("europart_data","Accounting",!'Accounts',!'Organizations',!'Years',!'Months',CONCATENATE(IF(MID(!'Periods',6,3) == "Jan",IF((VALUE(MID(!'Periods',1,4)) - 1) < 2008,"2008/Jan",CONCATENATE(STR(VALUE(MID(!'Periods',1,4)) - 1,4,0),"/Dec")),PALO.EPREV("europart_data","Periods",!'Periods'))," MTD"),"Forward")

    It will check if Period member is having January if so
    It will check if last year is lesser than 2008 ( Your first year) if so it will set it to default i.e Jan 2008 value.
    If not then it will set it to last year's Dec example Period 2010/Jan it will set it to 2009/Dec

    If not January then it will work as you desired - previous Period.

    Hope it helps!

    Best regards,
    Noel.