Use middle leaf value for consolidation

[AlanJ]

Hi

I wonder if anyone can help me

Rule Editor

['Register']=N:IF(ATTRS('CRL BAR Period',!CRL BAR Period,'Present')@='TODAY',DB('Test1',!Branch,!CRL BAR Period,!CRL BPR Scenario,'Register Available'),STET);

['Register available']=C:If(ELLEV(' BAR PERIOD',!BAR PERIOD)<>0 ,DB('Test1',!CRL Branch,!BAR Period,!Scenario,'Register'),STET);


Dimensions

Bar Period
Scenario
KPI
Branch


What im trying to do is override the consolidation with TODAYS input but leave the other consolidation with the last element in there consolidation.

This is what i would like
Actual
Register Available

SO-----------Year 2011----------------------JAN---DAY 1---DAY 2-----DAY 3---- DAY 4--------FEB--DAY1 --DAY 2----DAY 3-----DAY 4
-------------------12---------------------------10----- 9---------8--------- 4----------10-----------12-------9------12 --------0--------- 0


Hope this make sense, any help would be really appricated

[Christopher Kernahan]

Hi,

I don't quite follow this. I can see you want to force a rule calculated consolidation but can you be a bit clearer here;

What im trying to do is override the consolidation with TODAYS input but leave the other consolidation with the last element in there consolidation.

and possibly use a screenshot to show what the expected result is.

[AlanJ]

Ive changed the code a little please see below

['Register']=n:IF(ATTRN('CRL BAR Period',!CRL BAR Period,'Presentnum')>0,DB('Test1',!CRL Branch,!CRL BAR Period,!CRL BPR Scenario,'Register Available'),DB('Test1',!CRL Branch,ELCOMP('CRL BAR Period',!CRL BAR Period,ELCOMPN('CRL BAR Period',!CRL BAR Period)),!CRL BPR Scenario,'Register Available'));


['Register Available']=C:If(ELLEV('CRL BAR PERIOD',!CRL BAR PERIOD)<>0 & ATTRN('CRL BAR Period',!CRL BAR Period,'Presentnum')>0 ,DB('Test1',!CRL Branch,ELCOMP('CRL BAR Period',!CRL BAR Period,ELCOMPN('CRL BAR Period',!CRL BAR Period)),!CRL BPR Scenario,'Register'),DB('Test1',!CRL Branch,'25/10/2011',!CRL BPR Scenario,'Register'));


if you see on the screen shoot i want Oct -11 to be 6000 but sept -11 to be 3000 the last leaf of the consolidation

and year 2011 to be as it is 6000

Thank for your reply i hope you understand a bit better now

[Duncan P]

Why not set the element weights on the time dimension so that for normal consolidated periods only the last child period counts? If you need to use the time dimension in another cube that has normal aggregation then create and maintain two time dimensions, one for each method. You might also need one for "time first" aggregation (opening balance?).

If you need different measures in the same cube to use different time aggregation methods then you could do your "time last" aggregation in a separate cube. Source the leaf data in the "time last" aggregation cube from appropriate measures in the main cube and source the consolidated data for the "time last" measures in the main cube from the aggregation cube.

[Christopher Kernahan]

You could create a system/mapping cube that has your time dimension and a string dimension, then store the last submission in the cube as a string, updating this whenever you run the upload process. Then supply this to your rule as a DB reference.

cube
sys_submission_reference
dim
CRL Bar Period
measure

2011 25/10/2011
Sep-11 30/09/2011
Oct-11 25/10/2011

note, I've simplified the cube structure;

[ 'Register' ] = C: DB( 'Test1', !Version, !Region, 'Register Available', DB( 'sys_submission_reference', !CRL Bar Period, 'string'));

Issue may be that it is very generically fed;

[ 'Register Available' ] => [ 'Register' ];

[AlanJ]

Thanks,

I Will try what you said and get back to you with my results