Feeders not working when formula has IF statement

[diegoweichafe]

Hi Guys,

I recently developed a rule that use an IF statement. But the values are not adding up into the consolidation.

The rule check if for every day (Reporting Period Daily dimension), the value is 0 or <>0 in the dimension (FA Measures dimension) for an element called "Entry Price". If the value is 0, it will use the DB function to reflect the value of the previous day in the element called "1_Entry Price" , if the previous day is 0 as well, it will use the value of the second previous day, and if the second previous day is 0 again, it will use the value of the third previous day. If the value is <>0, it will just use the value of the day:

This is the cube:

FundAcc1.png (4.82 KiB) Viewed 4118 times

This is my code

Code: Select all

['1_Entry Price']= IF(
DB('Fund Accounting Daily',!Portfolio Code,DIMNM('Reporting Period Daily',DIMIX('Reporting Period Daily', !Reporting Period Daily)),'Entry Price')=0 & 
DB('Fund Accounting Daily',!Portfolio Code,DIMNM('Reporting Period Daily',DIMIX('Reporting Period Daily', !Reporting Period Daily)-1),'Entry Price')=0 & 
DB('Fund Accounting Daily',!Portfolio Code,DIMNM('Reporting Period Daily',DIMIX('Reporting Period Daily', !Reporting Period Daily)-2),'Entry Price')=0

,DB('Fund Accounting Daily',!Portfolio Code,DIMNM('Reporting Period Daily',DIMIX('Reporting Period Daily', !Reporting Period Daily)-3),'Entry Price'),

IF(
DB('Fund Accounting Daily',!Portfolio Code,DIMNM('Reporting Period Daily',DIMIX('Reporting Period Daily', !Reporting Period Daily)),'Entry Price')=0 & 
DB('Fund Accounting Daily',!Portfolio Code,DIMNM('Reporting Period Daily',DIMIX('Reporting Period Daily', !Reporting Period Daily)-1),'Entry Price')=0

,DB('Fund Accounting Daily',!Portfolio Code,DIMNM('Reporting Period Daily',DIMIX('Reporting Period Daily', !Reporting Period Daily)-2),'Entry Price'),

IF(
DB('Fund Accounting Daily',!Portfolio Code,DIMNM('Reporting Period Daily',DIMIX('Reporting Period Daily', !Reporting Period Daily)),'Entry Price')=0
,

DB('Fund Accounting Daily',!Portfolio Code,DIMNM('Reporting Period Daily',DIMIX('Reporting Period Daily', !Reporting Period Daily)-1),'Entry Price')

,DB('Fund Accounting Daily',!Portfolio Code,DIMNM('Reporting Period Daily',DIMIX('Reporting Period Daily', !Reporting Period Daily)),'Entry Price'))));

Feeders;

['Entry Price']=>['1_Entry Price'];

These are the results

FundAcc2.png (36.46 KiB) Viewed 4118 times

I'm not quite sure what's the feed that I need to develop to make this add up to the consolidation (clearly mine is incorrect).

I'd really appreciate if you guys could help me out.

Thank you very much

[Steve Rowe]

You just need to replicate your conditionality in the feeder as well.

You need to feed from today to tomorrow for the cases where these if no input, but watch out for breaking the feeder stack. Suggest you review the various threads on the feeder stack and long chains of feeders.

I'd also suggest that you move to using attributes to deliver your tomorrow / yesterday element, using the index to do this is pretty risky. Your code will break if the dimension structure changes, something that is very hard to ensure will never happen in the long term.

[diegoweichafe]

You just need to replicate your conditionality in the feeder as well.

You need to feed from today to tomorrow for the cases where these if no input, but watch out for breaking the feeder stack. Suggest you review the various threads on the feeder stack and long chains of feeders.

I'd also suggest that you move to using attributes to deliver your tomorrow / yesterday element, using the index to do this is pretty risky. Your code will break if the dimension structure changes, something that is very hard to ensure will never happen in the long term.

I'm not quite sure about the syntax of the feeder... I've been trying a few things.. but nothing seems to work...

[Wim Gielis]

It would help if you post your actual feeders and show us what you tried.

[diegoweichafe]

It would help if you post your actual feeders and show us what you tried.

This is my last try

Code: Select all

['Entry Price'] =>  DB('Fund Accounting Daily',!Portfolio Code,DNEXT('Reporting Period Daily', !Reporting Period Daily), '1_Entry Price');

[Steve Rowe]

Your logic if probably failing as you only want to roll forward three days, normally this is written so that value roll forward indefinitely. This logic outlined below.

Note also that I wouldn't use the dimension "step" formulas like Dnext, these create a relationship between the structure of your dimensions and your business logic. This relationship is hard to manage and control in the long term.

Create two attributes, 'Next' and 'Prior' and populate them with the relevant values. Your feeder should then become.

Code: Select all

['Entry Price'] =>  DB('Fund Accounting Daily',!Portfolio Code,Attrs('Reporting Period Daily', !Reporting Period Daily, 'Next'), '1_Entry Price');

and you rule

Code: Select all

['1_Entry Price']= 
N:IF(['1_Entry Price'] =0,
DB('Fund Accounting Daily',!Portfolio Code,Attrs('Reporting Period Daily', !Reporting Period Daily, 'Prior'), '1_Entry Price'),
(['1_Entry Price'] );

If you want to stick with rolling the values forward three days only (and do, if this works as you won't need to worry about the feeder stack). You'll need three feeders;

Code: Select all

['Entry Price'] =>  ['1_Entry Price'] ,
DB('Fund Accounting Daily',!Portfolio Code,Attrs('Reporting Period Daily', !Reporting Period Daily, 'Next'), '1_Entry Price'),
DB('Fund Accounting Daily',!Portfolio Code,Attrs('Reporting Period Daily',Attrs('Reporting Period Daily', !Reporting Period Daily, 'Next') , 'Next'), '1_Entry Price'),
DB('Fund Accounting Daily',!Portfolio Code,Attrs('Reporting Period Daily',Attrs('Reporting Period Daily',Attrs('Reporting Period Daily', !Reporting Period Daily, 'Next') , 'Next') , 'Next'), '1_Entry Price');

HTH

[diegoweichafe]

thanks for your help steve.. I implemented the changes and now is working. This is the final code:

Code: Select all

['1_Entry Price']= N: IF(
DB('Fund Accounting Daily',!Portfolio Code,DIMNM('Reporting Period Daily',DIMIX('Reporting Period Daily', !Reporting Period Daily)),'Entry Price')=0 & 
DB('Fund Accounting Daily',!Portfolio Code,DIMNM('Reporting Period Daily',DIMIX('Reporting Period Daily', !Reporting Period Daily)-1),'Entry Price')=0 & 
DB('Fund Accounting Daily',!Portfolio Code,DIMNM('Reporting Period Daily',DIMIX('Reporting Period Daily', !Reporting Period Daily)-2),'Entry Price')=0

,DB('Fund Accounting Daily',!Portfolio Code,DIMNM('Reporting Period Daily',DIMIX('Reporting Period Daily', !Reporting Period Daily)-3),'Entry Price'),

IF(
DB('Fund Accounting Daily',!Portfolio Code,DIMNM('Reporting Period Daily',DIMIX('Reporting Period Daily', !Reporting Period Daily)),'Entry Price')=0 & 
DB('Fund Accounting Daily',!Portfolio Code,DIMNM('Reporting Period Daily',DIMIX('Reporting Period Daily', !Reporting Period Daily)-1),'Entry Price')=0

,DB('Fund Accounting Daily',!Portfolio Code,DIMNM('Reporting Period Daily',DIMIX('Reporting Period Daily', !Reporting Period Daily)-2),'Entry Price'),

IF(
DB('Fund Accounting Daily',!Portfolio Code,DIMNM('Reporting Period Daily',DIMIX('Reporting Period Daily', !Reporting Period Daily)),'Entry Price')=0
,

DB('Fund Accounting Daily',!Portfolio Code,DIMNM('Reporting Period Daily',DIMIX('Reporting Period Daily', !Reporting Period Daily)-1),'Entry Price')

,DB('Fund Accounting Daily',!Portfolio Code,DIMNM('Reporting Period Daily',DIMIX('Reporting Period Daily', !Reporting Period Daily)),'Entry Price'))));


feeders;

['Entry Price'] => ['1_Entry Price'];
['Entry Price'] =>  DB('Fund Accounting Daily',!Portfolio Code,DNEXT('Reporting Period Daily', !Reporting Period Daily), '1_Entry Price');
['Entry Price'] =>  DB('Fund Accounting Daily',!Portfolio Code, DNEXT('Reporting Period Daily', DNEXT('Reporting Period Daily', !Reporting Period Daily)), '1_Entry Price');
['Entry Price'] =>  DB('Fund Accounting Daily',!Portfolio Code,  DNEXT('Reporting Period Daily', DNEXT('Reporting Period Daily', DNEXT('Reporting Period Daily', !Reporting Period Daily))), '1_Entry Price');