Relative proportional spread and rule calculated reference

[sander]

Hello all,

I have a question about Relative proportional spread, Is it possible to use Relative proportional spread (RPS) whit a rule calculated reference.
Example (simplified)
I have a cube wirth the dimensions;
- Product
- Month
- Measure

I hav the next view for product A

ScreenHunter_ 2012-01-18 13.10_01.jpg (14.86 KiB) Viewed 7914 times

Now I can use RPS when I want to fill ´Total Year´ for the measure ´Number This year' and use ´Number last year´ as a reference, this works fine.

Now I have another option where the customer wants to spread the data 40/60 (40% for the first 6 months and 60% for the next six months), I created a rule calculated element 40/60 to use as a reference;

ScreenHunter_ 2012-01-18 13.20_02.jpg (19.5 KiB) Viewed 7914 times

When I try to us RPS now, I get an error message;

ScreenHunter_ 2012-01-18 13.23_03.jpg (9.65 KiB) Viewed 7914 times

I can't find anything in the manual or this forum about this behaviour. Can anyone help me out here

I can think of two alternatives;
- Use a reference cube and fill this with a TI process (Memory efficient, if you use a small ref cube, but less user friendly because the selection of the ref cell needs more selections).
- Use TI to fill the reference element. (Memory inefficient, but user friendly because you only have to make one selection)

[lotsaram]

It depends what you are trying to spread on and what exactly the rule is. Provided the rule is defined with only one flex variable then TM1 will allow spreading on a rule cell and force the spread into the flex variable where the actual input data is. However if there is any ambiguity as to which variable to flex or if it is a complex rule then spreading will fail.

[sander]

When I use this rule for the referece value, it isn't working;

Code: Select all

['Product A','40/60',{'01','02','03','04','05','06'}] = N:40/6;
['Product A','40/60',{'07','08','09','10','11','12'}] = N:60/6;

[sander]

Sorry, this is working in the simple test cube I made, only not yet in the original cube

[declanr]

Sander,

The rules you have written are effectively saying that the first 6 months are 40/6 and the second 6 months are 60/6... this is the equivalent of hardcoding. There are no variables to make the rule change. It is simply saying it will always be that number regardless of anything else going on in the cube.

Personally if I was to want that I would create an N-Level input cell and then have a rule spreading that value to the periods by proportion. That N-Level is not in the consolidation but via the rule the consolidation would equal the same amount.

[sander]

I think I found the problem, the reference cell has to be fed. When I turn feeding on in the test cube and I don't feed it isn't working;

Works;

Code: Select all

['Product A','40/60',{'01','02','03','04','05','06'}] = N:40/6;
['Product A','40/60',{'07','08','09','10','11','12'}] = N:60/6;

Doesn't work;

Code: Select all

Skipcheck
['Product A','40/60',{'01','02','03','04','05','06'}] = N:40/6;
['Product A','40/60',{'07','08','09','10','11','12'}] = N:60/6;
Feeders;

[sander]

@declanr

That's a concern for later, I first want to have the spreading work

[lotsaram]

You're not showing us the right set of rules & feeders. As Declan has correctly pointed out the '40/60' rule is a hardcoded reference or numerical constant which you can't possibly spread over. So what value are you actually attempting to spread over and what is the definition of that intersection?

[sander]

Indeed you are both right, dropped my conclusion to fast. It isn't possible to feed it without having a real input value somewhere. I have now made a TI to get the data in place and skipped the rule. You could indeed also use the solution Declan mentioned, this is probably better because you can point to a cell where you get the 40/60 from and you could make it 50/50 or any other on the fly.