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

[matancha]

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,

Many thanks in advance -

Matan,

[marcus_sommer]

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

[matancha]

Marcus - thanks for your answer,

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.

[matancha]

Somebody?

[marcus_sommer]

You could try it with aggr:

sum(aggr(YourExpression, Dimension1, Dimension2))

- Marcus

[matancha]

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

Tnx -

Matan

[marcus_sommer]

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