topic Pivot Table Totals not always displayed in QlikView

Pivot Table Totals not always displayed

Fri, 18 Mar 2011 15:42:04 GMT

I have a pivot table as below which is only showing a small extract of the data. The problem I have is that unless I really filter in on the data the total for Value Added is not displayed.

product	fin_year	Value GBP	Value Added
MBEU179090		9934	7328
MBEU179189		8753	7861
MBEU179444		4224	3401
MBEU179445-1		2390	1991
MBEU179602-1		3599	3061
MBEU179672		3223	2606
MBEU185143		1570	1414
MBEU200440		18351	14652
MBEU200543		6716	5134
MBEU205111		1149	1149
MBEU205118		4460	4460
MBEU215103		1098	1098
Total		65466

If I select each product in turn then the total is displayed

product	fin_year	Value GBP	Value Added
MBEU179672		3223	2606
Total		3223	2606

Any ideas what is causing this, the Value GBP field is setup in the same was but always returns a value

Pivot Table Totals not always displayed

Fri, 18 Mar 2011 15:57:42 GMT

How is the expression?

often the expression not shows the total amounts, because not sum to the dimensions, for example,when doing sums in if statements

regards

Pivot Table Totals not always displayed

spividori — Fri, 18 Mar 2011 18:52:34 GMT

Hi!

You should use the function aggr.

Regards.

Re:Pivot Table Totals not always displayed

Oleg_Troyansky — Fri, 18 Mar 2011 19:46:24 GMT

I suspect you might have a part of your expression that can't be uniquely calculated at a total level, in which case you should either enclose all of your components within your aggregation function, or use aggr() to calculate the expression at a lower level of detail and then aggregate those pre-calculated results.

If you show your expression, it would be easier to pin-point the problem

Re:Pivot Table Totals not always displayed

Mon, 21 Mar 2011 17:13:35 GMT

My expression as it stands is

Sum(order_book_value)-(standard_material*sum(sales_order_qty))

Re:Pivot Table Totals not always displayed

Oleg_Troyansky — Mon, 21 Mar 2011 17:23:37 GMT

Exactly what I suspected... your field standard_material may return multiple possible values at the total level, and that renders the whole expression as a null. I'd recommend 2 changes:

1. Include standard_material within the sum()

2. Use function RANGESUM, to avoid getting null() when one of the two sum() returns a null() or missing.

The end result should look like this:

RANGESUM( Sum(order_book_value), -1 * sum(standard_material*sales_order_qty))

I believe this should work.