9 Replies Latest reply on Feb 18, 2020 7:10 PM by 2904959

    Member formula to evaluate prior period and year

    2904959

      Hello,

      I have a member formula where the logic must change to evaluate the prior month and year to the current month and year for the same record.  Here's a simplified version of my outline

       

      DIM period where members are Jan to Dec

      DIM years where members are FY15 to FY20

      DIM costcenter where members are CC1, CC2, etc

      DIM account where members are status, etc

       

      I have 2 records

      record 1: Jan, FY19,  CC1, status

      record 2: Jan, FY19,  CC2, status

       

      I need to evaluate record 2 and check if prior period = current period, and if prior year = current year, then set status = 5

       

      I'm working with @prior() but am not getting the expected results - I'm hoping it's a simple logic error.  Any thoughts is greatly appreciated - Thank you

      cl

        • 1. Re: Member formula to evaluate prior period and year
          2904959

          Any thoughts? Just want to query the prior record if a certain criteria is met with the current record, then I need to set the value of an account...  Thanks.

          • 2. Re: Member formula to evaluate prior period and year
            user111989

            it would help if you can provide an example

            • 3. Re: Member formula to evaluate prior period and year
              2904959

              Thank you.

              Here's is a simplified version of what I'm trying to do.  I've tried several variations within the if statement but the condition ends up in the else statement. 

              I need to find if the prior record's value is a 5, and if so, then override the existing value of the current record (a value of 4), and assign to the current record a value of -1, thus

              Dec->2019->Costcenter2->Product2->Action   =  -1

              ...

              if( @shift(@currmbr( "Period"->"Year"->"Costcenter"->"Product" ), -1 )  == 5 )

                  "Action" = -1;

              else

                  "Action" = -2;

              ...

               

              DIM1,       DIM2,      DIM3,              DIM4,          DIM5

              Period,    Year,        Costcenter,      Product,       Account     Value

              ...

              Dec,         2019,       Costcenter2,   Product1,     Action         5

              Dec,         2019,       Costcenter2,   Product2,     Action         4

              ...

              • 4. Re: Member formula to evaluate prior period and year
                user111989

                Ok, if you want to go to prior year you can try this

                 

                @PRIOR("Action"->Dec, 1, @CHILDREN("Years"))

                 

                can check if it works

                • 5. Re: Member formula to evaluate prior period and year
                  2904959

                  Small correction to my example above.  I removed "Product" from the @shift() call.  So if current record is

                  Dec->2019->Costcenter2->Product2, I want to retrieve the value of the prior record Dec->2019->Costcenter2->Product1

                   

                  if( @shift(@currmbr( "Period"->"Year"->"Costcenter" ), -1 )  == 5 )

                      "Action" = -1;

                  else

                      "Action" = -2;

                  ...

                   

                  DIM1,       DIM2,      DIM3,              DIM4,          DIM5

                  Period,    Year,        Costcenter,      Product,       Account     Value

                  ...

                  Dec,         2019,       Costcenter2,   Product1,     Action         5

                  Dec,         2019,       Costcenter2,   Product2,     Action         4

                  ...

                  • 6. Re: Member formula to evaluate prior period and year
                    2904959

                    hi, I tried with @prior also, but the result were similar,  Oddly, when I do a comparison it works, ie

                     

                        if(         (@shift(@currmbr( "costcenter" ), -1 )  == @currmbr( "Costcenter" ))  and

                            /* (@prior(@currmbr( "Period" ), 1 )  == @currmbr( "Period" )) and */

                             (@shift(@currmbr( "Year" ), -1 )  == @currmbr( "Year" )) )

                     

                    now if I could only capture the value of the prior record...

                    • 7. Re: Member formula to evaluate prior period and year
                      Cord234

                      I'm confused.  Are you trying to pull the prior period value like you said in your first post:

                       

                      "I need to evaluate record 2 and check if prior period = current period, and if prior year = current year, then set status = 5"

                       

                      or are you trying to pull the prior sibling of the current product:

                       

                      "

                      So if current record is

                      Dec->2019->Costcenter2->Product2, I want to retrieve the value of the prior record Dec->2019->Costcenter2->Product1"

                       

                       

                       

                      • 8. Re: Member formula to evaluate prior period and year
                        2904959

                        Product can be ignored in this case,

                         

                        I want the value of the prior record as long as the current Costcenter, the current Year, and the current Period match those from the prior record.

                         

                        thanks.

                        • 9. Re: Member formula to evaluate prior period and year
                          2904959

                          hhmm, I guess it's a bit more challenging with OLAP versus relational.  Would love to get that done within a member formula only because it fits the data flow..