ANY formula issue

LucaRicci

Hello,

I can't seem to properly make this aggregation formula work correctly because it's based off a boolean which apparently is not filtering the list items correctly.

TARGET MODULE, WKK51 Sales Returns - Actual/Forecast

lists: week, scenario, customer group. timescale: NOT APPLICABLE

SUM Formula:

IF    'SYS59 P&L Weekly Weeks & Scenario'.Past? AND Sales Returns Override Value <> 0 AND Override?THEN    Sales Returns Override Value

ELSE    IF        

Sales Returns?    THEN       

 'DAT11 Revenues'.Amount IFRS[SUM: 'DAT11 Revenues'.'P&L Weekly - Week (6 Weeks, Forecast)', SUM: 'DAT11 Revenues'.'P&L Weekly - Scenario (Forecast)', SUM: 'DAT11 Revenues'.Customer Group]    

ELSE        0

"Sales Returns?" Boolean:

'DAT11 Revenues'.'Sales Return? pt1'[ANY: 'DAT11 Revenues'.Customer Group, ANY: 'DAT11 Revenues'.'P&L Weekly - Scenario (Forecast)', ANY: 'DAT11 Revenues'.'P&L Weekly - Week (6 Weeks, Forecast)']

this formula returns this value:

which is the sum of all the values in the last table:

However this is not correct. In fact, the only value which should be selected is 1,548.

The "Sales Returns?" boolean returns this values in the target module, which is correct:

and in the SOURCE MODULE, DAT11 Revenues (lists, ID Revenues#, Timescale: NOTAPPLICABLE) the boolean "Sales Returns?" return this values:

Also this result is correct.

Then I wonder why the SUM formula considers also other rows such as row #17524 and so on:

I just don't get this, is it something with the ANY formula of the boolean in the target module?

How can I workaround this?

Also please note that the summary of this boolean has been set to "none" in the source module.

Thank you in advance,

Luca

@Dikshant another one ;)

Sai_Bharadwaj_Venati

Your Boolean is applied to your target module…which means it will only select combinations where sales return is present, which in this case is "Customer Group DT". However the Sum formula which we wrote doesn't have any idea which combination it should take or ignore from Source. It simply aggregates all values for all customer group, since we have Boolean selection at Customer Group DT it is showing over there and other places it is showing zero

Coming to solution, my recommendation would be creating a new line item in source module with formula ⇒ If not Sales return then 0 else value and final output will look something like this

IF    'SYS59 P&L Weekly Weeks & Scenario'.Past? AND Sales Returns Override Value <> 0 AND Override?THEN    Sales Returns Override Value

ELSE      

'DAT11 Revenues'.IFRS Sales return [SUM: 'DAT11 Revenues'.'P&L Weekly -Week (6 Weeks, Forecast)', SUM: 'DAT11 Revenues'.'P&L Weekly Scenario (Forecast)', SUM: 'DAT11 Revenues'.Customer Group]