How to FEED??? Going crazy...

[tobtm1]

Hi

I am starting to loose my mind here. I am desperately trying to get a feeder to work but nothing seems to work. Am I experienced in TM1? No, not really. I could really need some help if anyone have the time.

The measure I would like to feed is called 'FC Sales USD'. That measure is defined like this:

['FC Sales USD'] = N:
IF(DB('S_aggregate','USD',!Customer,'All Months',!Year,!Plant,!Product aggregate,!Version,'Calculated probability') @='100%',
(['FC Calculated sales'] * 1),
IF(DB('S_aggregate','USD',!Customer,'All Months',!Year,!Plant,!Product aggregate,!Version,'Calculated probability') @='90%',
(['FC Calculated sales'] * 0.9),
IF(DB('S_aggregate','USD',!Customer,'All Months',!Year,!Plant,!Product aggregate,!Version,'Calculated probability') @='60%',
(['FC Calculated sales'] * 0.6),
IF(DB('S_aggregate','USD',!Customer,'All Months',!Year,!Plant,!Product aggregate,!Version,'Calculated probability') @='30%',
(['FC Calculated sales'] * 0.3),
IF(DB('S_aggregate','USD',!Customer,'All Months',!Year,!Plant,!Product aggregate,!Version,'Calculated probability') @='10%',
(['FC Calculated sales'] * 0.1),
['FC Calculated sales'] * 1)))));

The measure 'FC Calculated sales' is defined like this:

['FC Calculated sales']=N:
IF(ATTRS('Product aggregate',!Product aggregate,'Volume_key') @='US_Dummy_customer',
[Currency:'N/A','FC Calculated volume'] * DB('S_aggregate','USD','US_Dummy_Customer',!Month,!Year,!Plant,!Product aggregate,!Version,'Price'),
[Currency:'N/A','FC Calculated volume'] * ['Price']);


And finally, just to be sure you have everthing needed, 'FC Calculated volume' is defined like this:

['FC Calculated volume'] = N:
IF(ATTRS('Product aggregate',!Product aggregate,'Volume_key') @='Dummy_customer',
DB('S_aggregate','N/A',!Customer,!Month,!Year,!Plant,ATTRS('Product aggregate',!Product aggregate,'Input_node'),!Version,'FC Volume') * ['Dummy_currency','Dummy_customer','Dummy_plant','Volume key'],
IF(ATTRS('Product aggregate',!Product aggregate,'Volume_key') @='US_Dummy_customer',
DB('S_aggregate','N/A',!Customer,!Month,!Year,!Plant,ATTRS('Product aggregate',!Product aggregate,'Input_node'),!Version,'FC Volume') *
(DB('S_ aggregate','Dummy_currency',!Customer,'NA_Numerator','NA_Year_Numerator','Dummy_plant',!Product aggregate,!Version,'NA_Numerator_value') /
DB('S_aggregate','Dummy_currency',!Customer,!Month,'NA_Year_Numerator','Dummy_plant',!Product aggregate,!Version,'NA_Denominator')),
DB('S_aggregate','N/A',!Customer,!Month,!Year,!Plant,ATTRS('Product aggregate',!Product aggregate,'Input_node'),!Version,'FC Volume') * DB('S_aggregate','Dummy_currency',!Customer,!Month,!Year,'Dummy_plant',!Product aggregate,!Version,'Volume key')));



My first choice was to basically write the feeder like:

['FC Calculated sales'] => ['FC Sales USD']

...but it doesnt work. Have tried so many different options. Please help...

Screenshot.PNG (11.03 KiB) Viewed 5070 times

[Steve Rowe]

Your rule will simplify to the following if you convert the text field to a number.

['FC Sales USD'] = N:
IF(DB('S_aggregate','USD',!Customer,'All Months',!Year,!Plant,!Product aggregate,!Version,'Calculated probability') @='', 1 ,

Numbr(DB('S_aggregate','USD',!Customer,'All Months',!Year,!Plant,!Product aggregate,!Version,'Calculated probability') )\100)

*
['FC Calculated sales'] ;

Your feeder looks correct, however it will only work if Calculated sales is fed as well, probably with calculated volume.

gl!

[tobtm1]

It actually didnt work... I have checked that:

['FC Calculated volume'] => ['FC Calculated sales']

and

['FC Calculated sales'] => ['FC Sales USD']

..but still problems. Must I go even further "back" in the chain? Because ['FC Calculated volume'] is depending on a measure called ['FC Volume']:

['FC Calculated volume'] = N:
IF(ATTRS('Product aggregate',!Product aggregate,'Volume_key') @='Dummy_customer',
DB('S_aggregate','N/A',!Customer,!Month,!Year,!Plant,ATTRS('Product aggregate',!Product aggregate,'Input_node'),!Version,'FC Volume') * ['Dummy_currency','Dummy_customer','Dummy_plant','Volume key'],
IF(ATTRS('Product aggregate',!Product aggregate,'Volume_key') @='US_Dummy_customer',
DB('S_aggregate','N/A',!Customer,!Month,!Year,!Plant,ATTRS('Product aggregate',!Product aggregate,'Input_node'),!Version,'FC Volume') * (DB('S_aggregate','Dummy_currency',!Customer,'NA_Numerator','NA_Year_Numerator','Dummy_plant',!Product aggregate,!Version,'NA_Numerator_value') /
DB('S_aggregate','Dummy_currency',!Customer,!Month,'NA_Year_Numerator','Dummy_plant',!Product aggregate,!Version,'NA_Denominator')),
DB('S_aggregate','N/A',!Customer,!Month,!Year,!Plant,ATTRS('Product aggregate',!Product aggregate,'Input_node'),!Version,'FC Volume') * DB('S_ aggregate','Dummy_currency',!Customer,!Month,!Year,'Dummy_plant',!Product aggregate,!Version,'Volume key')));

and 'FC Calculated Volume' is fed with a syntax I havent wrote myself and basically do not understand:

['All Input','FC Volume'] => ['All Products','FC Calculated volume'];

I do not know why to use double "refrences" on both sides of the => ?

[tomok]

The thing to understand about feeders is that they are nothing more than flags to tell TM1 where rule calculated values are being stored. They have absolutely no relation to the actual values being calculated so don't get hung up on the chain of calculations in your example. The key question is what determines when FC Sales USD has a value or not. Isn't it volume? If you don't sell any units (volume) then you don't have any sales, regardless of what currency you're talking about. The volume number is stored in the element called FC Volume. Why not just use that to feed Sales just like it is being used to feed FC Calculated Volume?

['All Input','FC Volume'] => ['All Products','FC Calculated volume'];
['All Input','FC Volume'] => ['All Products','FC Calculated Sales'];
['All Input','FC Volume'] => ['All Products','FC Sales USD'];

P.S. You've probably got some serious overfeeding in your volume numbers. Unfortunately, I don't have the bandwidth to try and decipher what your cube is doing. Overfeeding is always an issue when you manually enter a value in a catch-all type element and then calculate which plant or department or whatever should have the sales based on some attribute. Yes the rule works but it's extremely difficult to keep from overfeeding.

[tobtm1]

Thank you all for your feedback with possible solutions.

I understand what you are saying Tomok. My problem is that I cant feed like you suggest:

['All Input','FC Volume'] => ['All Products','FC Calculated volume'];

The reason for this is:

The elementreference 'All Input' on the left hand side is from the productdimension. The problem is that the productdimension is splited in two hierachies without a top consolidated element. This is because of the fact that Europe uses the 'All Input' hierarchy and North America is using the 'All Input NA' hierachy.

This is how the dimension looks like:

Capture.PNG (5.19 KiB) Viewed 4978 times

I guess it doesnt really matter why it is like this - but it is. I might suggest to the customer that I can consolidate them with a top level, but anyway..

So, to my question is it possible to feed 'FC Calculated volume' twice (!). Is that even possible? Like:

['All Input','FC Volume'] => ['All Products','FC Calculated volume'];
['All Input NA','FC Volume'] => ['All Products NA','FC Calculated volume'];

But before, I will anwer my own theory (partly). I changed to the above for NA and it didnt work. My theory know is because of the fact that FC Volume is fed by Volume and Volume dosent have a value in future years where FC Volume has becasue I use FC Volume to look at previous year and multiply by a "factor"

['FC Volume'] = N:
IF(DB('S_aggregate',!Currency,!Customer,!Month,!Year,!Plant,!Product aggregate,!Version,'Calculated status') @='Volume' & DB('S_aggregate',!Currency,!Customer,!Month,ATTRS('Year',!Year,'Prev_Year'),!Plant,!Product aggregate,!Version,'Volume') <1,
DB('S_aggregate',!Currency,!Customer,!Month,ATTRS('Year',!Year,'Prev_Year'),!Plant,!Product aggregate,!Version,'FC Volume') * DB('lrpYearlyIndex',!Customer,!Product aggregate,!Year,!Version,'Increase%'),
IF(DB('S_aggregate',!Currency,!Customer,!Month,!Year,!Plant,!Product aggregate,!Version,'Status') @='Manual',
DB('S_aggregate',!Currency,!Customer,!Month,!Year,!Plant,!Product aggregate,!Version,'Volume'),
DB('S_aggregate',!Currency,!Customer,!Month,ATTRS('Year',!Year,'Prev_Year'),!Plant,!Product aggregate,!Version,'Volume') * DB('lrpYearlyIndex',!Customer,!Product aggregate,!Year,!Version,'Increase%')));

This (I guess) means that:

1) I try to feed FC Volume with Volume but Volume for that particular Year/Month is 0. FC Volume is then feeding FC Calculated volume which then feeds FC Calculated sales which at last feeds FC Sales USD.

I know you said do not think of it like a "chain" but I cant see any other problems in my feeding.

Finally, I KNOW that this is almost completely impossible to follow and understand without seeing the code itself. By that said, thanks for your help anyway.

Best regards,

Tobias

[tomok]

My point is to feed sales EXACTLY the same way you are feeding volume, meaning the exact same source. You can't have sales without volume so if volume is being fed properly (in this case I think you mean FC Calculated Volume), then if you feed Sales from the same source then it will be fed properly. Trying to daisy chain feeders is meaningless and only adds confusion.

[tobtm1]

Thanks Tomok,

I hear you...

I continue the struggle and your input has been valuable.

/ Tobias

[Steve Rowe]

I think you need to put a next year attribute into your system / feeder.

[FC Volume]=>DB('S_aggregate',!Currency,!Customer,!Month,ATTRS('Year',!Year,'Next_Year'),!Plant,!Product aggregate,!Version,'FC Volume');

and

[Volume]=>DB('S_aggregate',!Currency,!Customer,!Month,ATTRS('Year',!Year,'Next_Year'),!Plant,!Product aggregate,!Version,'FC Volume');

?
Note that until you understand the impact of this take care, as you will create a chain of feeders that will cascade through your system into the future, I suggest you only populate the Next_year attr in years that you want to calculate.
HTH

[tobtm1]

Steve Rowe... MY MAN!

I have been struggling with the fact that there are nothing to feed on because FC Volume are calculated in previous years and then "sent forward" by a rule I have. I actaully even thought about creating a measure called "Dummy_measure" and set it to "1" and then feed on ['Dummy_measure'] => ['FC Volume'] cause I started to feel desperate...

BUT, this was spot on! Still have a question though....

You suggest that:


[FC Volume]=>DB('S_aggregate',!Currency,!Customer,!Month,ATTRS('Year',!Year,'Next_Year'),!Plant,!Product aggregate,!Version,'FC Volume');

and

[Volume]=>DB('S_aggregate',!Currency,!Customer,!Month,ATTRS('Year',!Year,'Next_Year'),!Plant,!Product aggregate,!Version,'FC Volume');


...but what does the redmarked really do? For me FC Volume feeds itself (kind of). The problem I have is that the bluemarked feeds the next year but I have five years ahead that need to be feed. Is it even possible to write like:

[Volume]=>DB('S_aggregate',!Currency,!Customer,!Month,ATTRS('Year',!Year,'Next_Year'),!Plant,!Product aggregate,!Version,'FC Volume') &
DB('S_aggregate',!Currency,!Customer,!Month,ATTRS('Year',!Year,'Next_Year2'),!Plant,!Product aggregate,!Version,'FC Volume') &
DB('S_aggregate',!Currency,!Customer,!Month,ATTRS('Year',!Year,'Next_Year3'),!Plant,!Product aggregate,!Version,'FC Volume') &
DB('S_aggregate',!Currency,!Customer,!Month,ATTRS('Year',!Year,'Next_Year4'),!Plant,!Product aggregate,!Version,'FC Volume') &
DB('S_aggregate',!Currency,!Customer,!Month,ATTRS('Year',!Year,'Next_Year5'),!Plant,!Product aggregate,!Version,'FC Volume');


Many thanks!

[Steve Rowe]

Your rule is something like this

[FC Volume] =
If ( x = true , [last years fc volume] * increase ,
If ( y=true , [this years volume] , [last years volume] * increase ))

and so is dependant on three things and so needs to be fed by the three things. If possible / practical you should work the conditionality of your rule into the feeders (it often isn't).
i.e.
[last years fc volume], [this years volume] and [last years volume] (I missed [this years volume] in my previous response)

In terms of feeding forward many years at once this is not required since the initial value feeds all the way forward for as far as the Next Year attributes are populated. if a value is populated in 2015 this triggers a feeder to 2016 which itself triggers a feeder to 2017 and so on...This is what I was talking about when I said you need to take care as if this goes on for too many steps (~255) you can break an internal stack and everything goes pear shaped. You should be OK with years though.