topic Re: Pivot Table - Partial Sums Problem (Sum, RangeSum, Aggr...) in QlikView

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

matancha — Mon, 07 Jul 2014 12:26:14 GMT

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:

I can't show the total of 'Material Requested Qty' column.
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,

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

marcus_sommer — Mon, 07 Jul 2014 15:08:30 GMT

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

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

matancha — Tue, 08 Jul 2014 05:18:04 GMT

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.

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

matancha — Tue, 08 Jul 2014 12:18:38 GMT

Somebody?

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

marcus_sommer — Tue, 08 Jul 2014 12:39:48 GMT

You could try it with aggr:

sum(aggr(YourExpression, Dimension1, Dimension2))

- Marcus

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

matancha — Tue, 08 Jul 2014 13:14:33 GMT

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

Tnx -

Matan

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

marcus_sommer — Tue, 08 Jul 2014 13:26:39 GMT

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