TM1 Forum

I have a rule in place for circulation statistics which is supposed to take the number from the last issue date of each month and apply it to consolidated element 'All Issue Dates' for the particular month. I have an attribute set up in the Time dimension which indicates the last issue date of each month ('CW Last Iss of Month'). However, the results are inconsistent. As you can see in the attachment, sometimes the rule works, sometimes it doesn't (it adds up all the issues in the month instead of taking just the last one). The rule is as follows:

Feedstrings;
Skipcheck;

#Pulls CW Mag circ and headcount data for the last issue of each month for monthly, quarterly, and annual roll-ups:
['3500 - Chemical Week Magazine - Print', {'AA','LL','PA','PJ','BA','C1','C2','C3','LL TEMP','WB','WP','IMSADJ','C2EXTADJ'},
{'Domestic - Controlled','International - Controlled','Domestic - Paid','International - Paid','Domestic Digital - Controlled','International Digital - Controlled','Domestic Digital - Paid','International Digital - Paid','Editorial','Ad Sales','Ad Promo','Publisher','Event Sales','Event Promo','Production','Logistics / Other'},'All Issue Dates']=
c:DB('finStatistics',!finBusinessUnits,attrs('Time',!Time,'CW Last Iss of Month'),!finLedgers,!Time,!finSatisticsMeasures);


feeders;
['Domestic - Controlled','International - Controlled','Domestic - Paid','International - Paid','Domestic Digital - Controlled','International Digital - Controlled','Domestic Digital - Paid','International Digital - Paid','Editorial','Ad Sales','Ad Promo','Publisher','Logistics / Other']=>
DB('3500 - Chemical Week Magazine - Print', ATTRS('Time', !Time, 'CW Last Iss of Month'),!finLedgers,'All Time',!finSatisticsMeasures);[/code]

The cube is called finStatistics and the dimensions are as follows:
finBusinessUnits
finIssueDates
finLedgers
Time
finSatisticsMeasures

Is there something wrong with my rule or feeder which would cause the rule to calculate correctly sometimes but not others?

Thanks for any suggestions.

Jennifer

First of all - what a very impressive set of documentation and explanation for a problem. You have a future role training my customers in how to do this!

On your query - my initial suspicion focused on the fact that the attribute you use is the same in the rule as in the feeder. Normally if you have an attribute driving a rule you need a second, 'inverse' attribute for the feeder. Though I will admit I'm not sure what that would be in your case. I'm even thinking that you may not need a feeder at all. Feeders do two jobs - they mark a calculated cell to be shown in a zero suppressed view, and they trigger consolidation. Can't see how either apply here - you are looking at a consolidated value with input children, so it will happen.

Finally, my (next) suspicion is around C level rules. They often seem to misbehave. 'blank' rules (KPIs etc) are fine - most other calcs are best done as N (artificially if necessary) and consolidated.

In your case, is your top right cell consolidated? If not, how does the value in your 3rd snapshot get there?

Maybe change the rule to 'blank'?

HTH - will be interested to see what works.

Hi Jennifer,

Assuming the consolidation "All Issue Dates" is a flat consolidation of all issue dates ever then as your rule currently stands you shouldn't need a feeder if some of the children of the consolidation contain real input values then consolidation is triggered and no feeder flag is required.

If this isn't the case and "All Issue Dates" does not include everything then you would need a feeder (which I don't think is the case). However your feeder is wrong as you are feeding from the last issue date back to "All Time" in your month/year (Time) dimension. What you want to do is feed "All Issue Dates" in your finIssueDates dimension for the relevant month which can be done with square bracket notation, or in long DB notation as you have it would be:
=> DB('finStatistics ','3500 - Chemical Week Magazine - Print', 'All Issue Dates',!finLedgers,!Time,!finSatisticsMeasures)

However to elucidate a little further on what David and I have already pointed out you shouldn't need to feed as feeding is an explicitly "N element concept". Both versions of your feeder will result in significant over-feeding as feeding a consolidated element has the effect of feeding all N level descendent. That is feeding "All Time" actually feeds Jan 2004, Feb 2004, Mar, 2004, ... Dec 2015 (or whenever your time dimension month N elements actually start and finish) and feeding "All Issue Dates" actually feeds 11/28-12/05, 12/12, 12/19-26, .....

Because of the overfeeding it could be that during loading of the server you are getting a stack overflow due to the recursive/circular nature of the feeder and rules for the cube are not set, if the rule is saved manually the rule compiles and you then see the correct calculation (in many versions of TM1 there seems to be additional stack space reserved for manual rule saving vs. evaluation of rules during server loading.)

Try it without the feeder and see if this fixes the problem.

However I would suggest a slight change of approach. All you want to do is to lookup the last issue date for each month. Rather than overriding a consolidation just replace 'All Issue Dates' with a new leaf (N level) element that is rule defined. To keep it simple just call it 'Last Issue of Month'. You will then need to feed but everything will be much more straightforward as it will be an N rule which is much better practice.

Eg. (I have simplified the LHS area statement for clarity)
Rule:
['Last Issue of Month'] = N:DB('finStatistics',!finBusinessUnits,attrs('Time',!Time,'CW Last Iss of Month'),!finLedgers,!Time,!finSatisticsMeasures);
Feeder:
['All Issue Dates'] => ['Last Issue of Month];
(Feeder is many => one, much less problematic than one => many.)


Whew, that was long winded. If you're still with me and have understood most of that then you're well on the way.

If your number does need feeding, is it not just a case that you have missed the cube name from the start of the DB() in the feeder?

feeders;
['Domestic - Controlled','International - Controlled','Domestic - Paid','International - Paid','Domestic Digital - Controlled','International Digital - Controlled','Domestic Digital - Paid','International Digital - Paid','Editorial','Ad Sales','Ad Promo','Publisher','Logistics / Other']=>
DB('finStatistics','3500 - Chemical Week Magazine - Print', ATTRS('Time', !Time, 'CW Last Iss of Month'),!finLedgers,'All Time',!finSatisticsMeasures);

Wow, many thanks for the very comprehensive and educational replies! I have removed the feeder as you all suggested, and it is calculating correctly right now. However, I'll check it over the next couple of days and if it again reverts to the incorrect calc, I will change the rule to N-level. I never thought about the idea that I was over-feeding.

David - I wrote this in a rush, and was worried I left something out, so I appreciate your comment. Part of my job is documenting business processes, so I 've had lots of practice.

I'll let you all know if I need to change the rule and how it works out.

Thanks again,

Jennifer

Just wanted to follow up with how this worked out, if anyone is interested.

First, I tried deleting the feeder. This worked at first, but then the rule reverted to adding up all the issues later. Secondly, I tried changing my existing rule to N-level. Again, this worked for a while and then reverted. Lastly, I tried Scott's approach to create an N-level element to hold the last issue, instead of using the consolidated 'All Issue Dates'. However, the N: in the rule was causing the quarters and years to add up instead of taking just the last issue. I deleted the N: and it has been working for about a week now, so it seems like the problem is resolved. Thanks Scott!

Jennifer