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

# Member formula to evaluate prior period and year

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

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

it would help if you can provide an example

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

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

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

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

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

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

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

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..