Aggregation Help

[ISK]

Hello Good people. Stuck at a problem.

What i'm trying to do is I want to sum the values for each individual material on the right.

For example: for the number 1000034697 we have 4 sub numbers 7000090312,7000090314,7000090315,7000090313 which each have their own values as 0,259776,277128,491832. Here's the formula for the right most column: 

=aggr(if(RequirementRSF < 0, 0, RequirementRSF),BOM_Material)

What I want is I want to create a separate table that would give me 1000034697 and its value as 1028736. Same for all the other number series. Some how my aggregation works fine only when I select one number from the left. But its not gives me zero if I do not select any number. Any advice?

[TauseefKhan]

Hi @ISK,

Check this one:

Dimension: BOMinBOM_Material
Measure:
Sum(Aggr(Sum(TOTAL <BOM_Material> if(RequirementRSF < 0, 0, RequirementRSF)), BOM_Material))

******Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.********

[ISK]

Unfortunately no. The formula gives zero values.

Here's what I tried:

aggr(sum(aggr(if(RequirementRSF < 0, 0, RequirementRSF), BOM_Material)),BOMinBOM_Material)

Nows here's the output I get if I dont select any BOMinBOM_Material:

And here's the output I get when I select only one Material. Somehow it works only when selecting one material.

[TauseefKhan]

No need to use Expression in first column: Just use BOM_Material



or you can go with Scripting:

Material_Data:
LOAD
BOM_Material,
BOM_Sub_Material,
RequirementRSF,
if(RequirementRSF < 0, 0, RequirementRSF) as Valid_RequirementRSF
INLINE [
BOM_Material, BOM_Sub_Material, RequirementRSF
1000034697, 7000090312, 0
1000034697, 7000090314, 259776
1000034697, 7000090315, 277128
1000034697, 7000090313, 491832
1000034699, 7000090318, 0
1000034699, 7000090319, 0
1000034699, 7000090314, 259776
1000034699, 7000090315, 277128
1000034700, 7000090318, 0
1000034700, 7000090319, 0
1000034700, 7000090314, 259776
1000034700, 7000090315, 277128
];
///*
Aggregated_Material_Data:
LOAD
BOM_Material,
Sum(Valid_RequirementRSF) as Total_RequirementRSF
RESIDENT Material_Data
GROUP BY BOM_Material;

*****Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.******

[Or]

Step 1: Get rid of the unnecessary if() in the measure. You can use RangeMax instead:

RangeMax(RequirementRSF,0)

Step 2: This looks like it should work with Total <Field> rather than needing aggr, so:

Sum(total <BOM_Material> RangeMax(RequirementRSF,0) )

Note that I'm not sure what the dimension's if() statement is about, so I don't know if that'll have any impact on the results.