Need the Sum of an Expression

[KPage]

HI! I am trying to calculate the "Sum" of Net OH Dollars and Net OH Reserves of each division. Right now, I can only see this (individual)  data if I add in a UPC. How do I get the totals for each division, without a UPC?

Current formulas below:

Net OH Dollars

[Net On Hand]*
(If(
[On Hand]+[Open SO]<>0,
[Standard Price - Document Currency -> USD]/[Standard Price - Unit],
Null()
)
)

Net OH After Reserves

[Net On Hand]*
(
if(isnull(
If(
[On Hand]+[Open SO]+[Reserved Qty]<>0 AND NOT IsNull([Mat.-Cost Reserve - Document Currency -> USD]),
[Mat.-Cost Reserve - Document Currency -> USD],
0
)
),
(If(
[On Hand]+[Open SO]<>0,
[Standard Price - Document Currency -> USD]/[Standard Price - Unit],
Null()
)
),
(If(
[On Hand]+[Open SO]<>0,
[Standard Price - Document Currency -> USD]/[Standard Price - Unit],
Null()
)
)-
(
If(
[On Hand]+[Open SO]+[Reserved Qty]<>0 AND NOT IsNull([Mat.-Cost Reserve - Document Currency -> USD]),
[Mat.-Cost Reserve - Document Currency -> USD],
0
)
)
)
)

[Oleg_Troyansky]

Hi!

It looks like your expressions don't include any aggregation functions... In Qlik, your detailed data needs to be aggregated in order to present totals by Customer, or Month, or Country, etc...

So, if the detailed field in the data is called "Amount", then the expression should be:

sum(Amount)

I also see many IF functions that have conditions based on individual values. These values can only be evaluated at the most detailed level. At the aggregate level, they will likely render a NULL value and the conditions will be always false. So, the simplest solution is to include the IF condition within the SUM(),  but that's way too slow for large datasets, so we are trying to avoid it. These conditions need to be processed either in the data layer, in the script, or in Set Analysis - but that's a lot more explanations that I can provide in the boundaries of a forum message.

I have to say, you would benefit a lot from some form of education - a training class, or a book (mine, for example), or a lot of blog reading.

Best,