pivot table totals

[#1]

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.

[Anonymous]

Hi, can you send an example qvw?

[whiteline]

Or type your expresison and say what are your dimnsions.

[#4]

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.

[Anonymous]

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))

[whiteline]

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'.

[#7]

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

[thomas_skariah]

Hi Matt,

For uploading a qvw first click on reply,go to Top(Right hand side) click on advanced editor,go the attachments and upload the file.Find the screenshots attached.

Regards,

Tom

[#9]

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.

Thanks in advance,

Matt

[#10 · Accepted solution]

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)