topic Re: pivot table totals in QlikView

pivot table totals

Fri, 31 Aug 2012 16:49:57 GMT

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

Anonymous — Sat, 01 Sep 2012 05:24:11 GMT

Hi, can you send an example qvw?

Re: pivot table totals

whiteline — Sat, 01 Sep 2012 09:23:43 GMT

Or type your expresison and say what are your dimnsions.

Re: pivot table totals

Sun, 02 Sep 2012 16:24:53 GMT

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

Anonymous — Sun, 02 Sep 2012 17:41:38 GMT

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

whiteline — Sun, 02 Sep 2012 17:48:58 GMT

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

Sun, 02 Sep 2012 18:20:00 GMT

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

thomas_skariah — Mon, 03 Sep 2012 02:20:42 GMT

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

Re: pivot table totals

Mon, 03 Sep 2012 18:59:35 GMT

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

Re: pivot table totals

Mon, 03 Sep 2012 19:34:35 GMT

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

Mon, 03 Sep 2012 21:12:53 GMT

that fixed it. Thank you.