Testing for current month and prior month

[Steve Rowe]

I've not been able to figure out a way of doing this so thought I'd see if someone has a smart way of doing this.

The problem
In a rule set I need to test if the period in the rule space is equal to the current or the prior month as set in another cube. This is trivial to do with two logic tests.
i.e.

Code: Select all

[]=N: If ( Numbr (!Year | !Month ) = DB ( ref to current month setting) % Numbr (!Year | !Month ) = DB ( ref to prior month setting), etc

In an effort to make my rules more efficent though I'd like to achieve the same logic using a single logic test so I don't have to repeat the lookup to the current month cube. The DB to the where the current month is held contains two other DBs in my actual system which is why I think its worth making the effort and I need to use the above logic to condionalise all my feeders.

Code: Select all

[]=N: If ( MOD ( DB ( ref to current month setting) , Numbr (!Year | !Month ))<=1 , etc

The above comes close but doesn't work when the current month is 201101 for example.

[Duncan P]

How about ...

Change the current month source to contain <EDIT>the string</EDIT>

Code: Select all

previous_month | ',' | current_month

then change the test to be

Code: Select all

0 < SCAN( !Year | !Month, DB( ref to current and previous month source ) )

Would that work?

[Steve Rowe]

Yes, thanks Duncan,
I actually thought of another way of doing this

If I calculate the things that I am testing like this

Code: Select all

Numbr (!Year) *12 + Numbr( !Month)

then the mod approach works since you gat a continuous sequence of numbers.
Cheers,