10 Replies Latest reply: Sep 3, 2012 5:12 PM by Matt Johnson

pivot table totals

I am working in a pivot table with an expression that is adding together the values in two different expressions.  The cell it being populated with the correct result, but the total for the column is zero it is not a dash but a zero.  all of the other columns are totaling OK.  I exported it to excel to confirm the correct total is not zero.

• Re: pivot table totals

Hi, can you send an example qvw?

• Re: pivot table totals

The expression is:

if([Tax unit of Property]='#N/A',[Total Value Replaced],if([Tax unit of Property]='Non-Stock item',[Total Value Replaced],0))

Tax Unit of property is a dimension

Total Value Replaced is another expression

I tried to copy/paste the qvw file into this reply.......but couldn't (really new to qv).  The file is pretty large (2MB) which I can share if I can figure out how to attach.

I may try to create a sample file that is a lot smaller while I wait for instructions on how to attach a file.  I will search here a bit more as well.

• Re: pivot table totals

Matt,

There is no total because there is no any aggregation function.  Try to put your whloe expression inside sum().

Regards,

Michael

Edit:  I think it should be like this:

sum(if([Tax unit of Property]='#N/A' OR [Tax unit of Property]='Non-Stock item',[Total Value Replaced],0))

• Re: pivot table totals

The problem is that your formula doesn't contain aggreagatoin.

When this expression evaluated for every value of [Tax unit of Property] it works.

Imagine that it's calculating for the total row. Instead of [Tax unit of Property] you have all set of values.

It's an error and QlikView evaluate this as NULL.

Then NULL='#N/A' => FALSE, NULL='Non-Stock item' => FALSE => you get your '0'.

• Re: pivot table totals

I tried including the entire expression within a sum function like suggested but it returned a zero for all lines.

Whiteline's comment makes sense to me I think (an accountant not a developer) why it works for each line, but not a total, but I am still unsure how to correct it.

How do I attach a qvw file?  I know it is difficult to really address without seeing the entire picture.

Thank you helping,

Matt

• Re: pivot table totals

Hi Matt,

Regards,

Tom

• Re: pivot table totals

Thanks for the file upload info.

Attached is the qvw file that contains the expression "Remove Non-Stock items and #N/A" which is working on a line by line basis but does not display a total.

Matt

• Re: pivot table totals

Hi Matt,

as whiteline mentioned above, the problem of the according expression is the missing aggregate-function. Without any of them, a summing up isn't possible. I rewrote your expression. It should work as I used two sums, each with your filter (if()) included.

Sum (if([Tax unit of Property]='#N/A' or [Tax unit of Property]='Non-Stock item', [Val/COArea Crcy],0))
+
Sum (if([Tax unit of Property]='#N/A' or [Tax unit of Property]='Non-Stock item', [Val/COArea Crcy])/50185493*79974406,0)

Regards

Roland

Sum (if([Tax unit of Property]='#N/A' or [Tax unit of Property]='Non-Stock item', [Val/COArea Crcy],0))
+
Sum (if([Tax unit of Property]='#N/A' or [Tax unit of Property]='Non-Stock item', [Val/COArea Crcy])/50185493*79974406)

• Re: pivot table totals

that fixed it.  Thank you.