Create rule for Projection

[herchi]

Hi guys,

I'm new to this forum and I've a small question. I'm trying to create a rule for a revenue projection.
The rule should work in this way: If a month is closed, then take the revenue else the revenue of last 6 months decided by 6.

I've created the following statement:

SKIPCHECK;
['Revenue PROJ']=if(ATTRN('Time', !Time, 'IsClosed') = 0, ['Revenue'], ['last 6 months','Revenue']/6);

FEEDERS;
['last 6 months','Revenue']=>['Revenue PROJ'];

I can see the correct values on N level but on C level I have a 0.
Why does the aggregation not work correctly? Do you have any ideas?

thanks and kind regards
Chris

[declanr]

Change your

Code: Select all

=

to an

Code: Select all

=N:

Search through the user guides for the difference between the 2, it will be in the same section as a number of other things that would be of benefit for you to read.

[Neil Watson]

Hi Chris,

2 quick things
1. if you want the rule to apply only to N level (the lowest level) elements then you need to include N: in your rule statement
['Revenue PROJ']= N: if(ATTRN('Time', !Time, 'IsClosed') = 0, ['Revenue'], ['last 6 months','Revenue']/6);

equally you can add a C: which will mean the rules applies to only consolidation elements.
If you don;t specify anything then the rule applies to both N level and C level elements

2. As your rule uses either, [Revenue] or last 6 months, you need to feed from both
['last 6 months','Revenue']=>['Revenue PROJ'];
['Revenue'] => ['Revenue PROJ'];

Hope this helps
Neil

[herchi]

many thanks for your quick replies.

@Neil: I've changed my feeder to you approach. But now the C level shows the wrong value. I've attached a screenshot. As you see the first value of my avg. revenue is now the value on the C level. But it should be the aggregation on revenue and ['last 6 months','Revenue']/6

Do you have any idea?

[herchi]

Does someone have an idea?

[declanr]

many thanks for your quick replies.

@Neil: I've changed my feeder to you approach. But now the C level shows the wrong value. I've attached a screenshot. As you see the first value of my avg. revenue is now the value on the C level. But it should be the aggregation on revenue and ['last 6 months','Revenue']/6

Do you have any idea?

If you want consolidated periods (such as "2013") to ignore the rule and just apply standard TM1 aggregation logic you will need to specify for it to do so, since you have an = (N & C) level rule for "Revenue Proj" every single intersection of the cube that contains "Revenue Proj" will have the rule apply.

Just add an extra IF statement into your rule to ignore the cells you don't want it working on e.g.

Code: Select all

['Revenue PROJ']= N: 
If ( DType ( 'Time', !Time ) @= 'N',
      if(ATTRN('Time', !Time, 'IsClosed') = 0, ['Revenue'], ['last 6 months','Revenue']/6),
      CONTINUE);

You will probably need to change the above rule a little bit to hit the cells that are/aren't relevant for you but just remember the logic of what your left hand side of the rule is referencing and you should be good to go.

[herchi]

i understand ... I used your approach, but the avg. revenue for the non closed months is missing now ... Please check the attachment ...

the rule:

Code: Select all

SKIPCHECK;
['Revenue PROJ']=N: if (DType ('Time', !Time) @= 'N', if(ATTRN('Time', !Time, 'IsClosed') = 1, ['Revenue'],['last 6 months','Revenue']/6), continue);

FEEDERS;
['last 6 months','Revenue']=>['Revenue PROJ'];
['Revenue']=>['Revenue PROJ'];

any ideas?

[declanr]

i understand ... I used your approach, but the avg. revenue for the non closed months is missing now ... Please check the attachment ...

the rule:

Code: Select all

SKIPCHECK;
['Revenue PROJ']=N: if (DType ('Time', !Time) @= 'N', if(ATTRN('Time', !Time, 'IsClosed') = 1, ['Revenue'],['last 6 months','Revenue']/6), continue);

FEEDERS;
['last 6 months','Revenue']=>['Revenue PROJ'];
['Revenue']=>['Revenue PROJ'];

any ideas?

Sorry Herchi, I had copied the rule above & altered it without really paying attention. I meant:

['Revenue PROJ']= if (DType ('Time', !Time) @= 'N', if(ATTRN('Time', !Time, 'IsClosed') = 1, ['Revenue'],['last 6 months','Revenue']/6), continue);

[herchi]

ah I understand ... now I've this result. But the total is wrong. 2013 should be about 69007196,07

Do you know what's wrong?

[declanr]

ah I understand ... now I've this result. But the total is wrong. 2013 should be about 69007196,07

Do you know what's wrong?

It might be beneficial if you could provide the full cube structure, including what exists under "Kunden"... but in the short term it will probably help you to look at the consolidatechildren function.

That being said we are possibly heading towards a not very efficient cube. Maybe upon providing your full cube structure & business requirement we will be able to point out a more suitable option. Since you are most of the way there on this though it is worth implementing the ConsolidateChildren function and see what impact it has on performance, it's just one of the functions I tend to be wary about. Especially if you implement it on a dim that is likely to grow significantly in the future.

[Jeroen Eynikel]

Hi

I think you need to go back to your first version rather than continue with the dtype check. Don't see why you'd need this in this case.



SKIPCHECK;
['Revenue PROJ']=if(ATTRN('Time', !Time, 'IsClosed') = 0, ['Revenue'], ['last 6 months','Revenue']/6);

FEEDERS;
['last 6 months','Revenue']=>['Revenue PROJ'];

I can see the correct values on N level but on C level I have a 0.
Why does the aggregation not work correctly? Do you have any ideas?

=> Change it into



SKIPCHECK;
['Revenue PROJ']=N: if(ATTRN('Time', !Time, 'IsClosed') = 0, ['Revenue'], ['last 6 months','Revenue']/6);

FEEDERS;

['Revenue']=>['Revenue PROJ'];
['last 6 months','Revenue']=>['2013','Revenue PROJ'];

I don't like the hardcoded 2013 in there myself but I would expect this resolves your issue.
The problem is that the months for which you don't have any revenue yet are not being fed as ['last 6 months','Revenue']=>['Revenue PROJ'] will only feed the months underneath your last 6 months aggregation, not the ones that you actually need to have fed - which are the future months.

Depending on how long your forecast period is a more elegant solution than hardcoding 2013 on the right side of your feeder would be to create a consolidation containing all the future months in yr forecast period and feed that.

I.e. something like
['last 6 months','Revenue']=>['Remaining Fcast Period','Revenue PROJ'];