How to use Set Analysis with Aggr()

[SchalkF]

Good morning all,

I have an issue with the following bit of code:

SUM(AGGR(
Sum(BPrimaryQty)
,[Document Number],[Act Line Num],[Item Code],BItemCode))
/
SUM(AGGR(nodistinct
Sum(BPrimaryQty)
,[Document Number],[Act Line Num],[Item Code]))

This calculates the percentage amount of BItemCode that is used in the Item Code.  Its basically a recipe percentage of a Base Item in the final product.

The issue I am having is that I need to add a set analysis that calculates this recipe even if I use a filter to exclude Base Items that has no sell value.

For instance lets say I have 3 Base Items:

• BItem A - 0.5t per 1 ton Final Item
• BItem B - 0.4t per 1 ton Final Item
• BItem C - 0.1 t per 1 ton Final Item

BItem A and B has a sell value, so they are included in the calculation, and BItem C has no sell value, and is excluded when I add the filter.

So the % for BItem A is calculated as 5/9 and BItem B is calculated as 4/9, which gives the wrong percentages in the next calculations.

I tried using the new syntax for Set Analysis where we add it to the start of the calculation:

{1<>}SUM(AGGR(Sum(BPrimaryQty)
,[Document Number],[Act Line Num],[Item Code],BItemCode))
/
SUM(AGGR(nodistinct
Sum(BPrimaryQty)
,[Document Number],[Act Line Num],[Item Code]))

But I get an error that states:

"Error in expression: '{' expected"

[md_talib]

i would suggest you to go with this way. 

first check this expression in a table column. 

SUM(AGGR(
Sum(BPrimaryQty)
,[Document Number],[Act Line Num],[Item Code],BItemCode))

again the second expression in another column 

SUM(AGGR(nodistinct
Sum(BPrimaryQty)
,[Document Number],[Act Line Num],[Item Code]))

if both the expression works then go with the this expression 

SUM(AGGR(
Sum(BPrimaryQty)
,[Document Number],[Act Line Num],[Item Code],BItemCode))
/
SUM(AGGR(nodistinct
Sum(BPrimaryQty)
,[Document Number],[Act Line Num],[Item Code]))

it will be easier for you to find the error.

[SchalkF]

Hi @md_talib 

Thanks for the reply but the formula works...

There are price managed products, like in my example item A and B, but item C is not price managed.

The users requires a filter to filter out non managed items.  So when we calculate the recipe after filtering out the non managed items, we get the wrong recipe, because the non managed Item is not available anymore, and the calculation is now only using 2 items, instead of 3.

I require a set analysis like {1<>} to exclude the filter of managed and non managed items to get the correct recipe for further calculations.

[md_talib]

hi @SchalkF 

if you want to exclude the C from your analysis. 

so you can you can try this in your set 

sum({<Managed={"A","B"}>}Value)

or 

sum({<Managed= - {"C"}>}Value)

you can include as well as exclude your desired output.