cancel
Showing results for
Did you mean:
Creator

## Pivot Table - Partial Sums Problem (Sum, RangeSum, Aggr...)

Hello all.

Please look in the attached file.

I need to calculate raw material demand, by month according to the flowing formula:

{ [Warehouse Balance] - [Accumulated Orders]} * [Material Coefficient]

For each material, i need the total demand for each month, but only if { [Warehouse Balance] - [Accumulated Orders]} is negative (i.e. --> no parts in warehouses --> need to produce)

My Table view:

I have 2 major problems:

1. I can't show the total of 'Material Requested Qty' column.
2. in months with no order I didn't get the result  for { [Warehouse Balance] - [Accumulated Orders]} * [Material Coefficient] .

Examples:

I really appreciate any help,

Matan,

6 Replies

Some calculations on row-level will return null - this could be solved with functions which return 0 or a customized value instead of null, for example: range-functione like rangesum() or alt():

alt([Warehouse Balance] - [Accumulated Orders]} * [Material Coefficient], 0)

- Marcus

Creator
Author

but i don't think it's the case here.

My 'Material Requested Qt 'expression is:

Num(

If( ((Sum(WarehousesBalance.Balance) - RangeSum(Before(Sum(OpenOrders.TBalance), 0, ColumnNo()))) < 0)

,( (Sum(WarehousesBalance.Balance) - RangeSum(Before(Sum(OpenOrders.TBalance), 0, ColumnNo()))) * RawMaterial.Coef )

)

, '#,##0.0')

It's contain the RangeSum, but maybe I need something more.

I search for solution how can I see totals with the above expressions (or with alternative expression).

Matan.

Creator
Author

Somebody?

You could try it with aggr:

sum(aggr(YourExpression, Dimension1, Dimension2))

- Marcus

Creator
Author

I'm tried it, but it also didn't work.

Tnx -

Matan

Your expressions are quite complex. I suggest you tried to split these expressions and simplified them and perhaps you find another ways to reach your aim. Sometimes there is only a small syntax issue ...

- Marcus

Community Browser