Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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.

1 Solution

Accepted Solutions
Not applicable

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)

10 Replies
filosofo
Contributor

Re: pivot table totals

Hi, can you send an example qvw?

whiteline
Honored Contributor II

Re: pivot table totals

Or type your expresison and say what are your dimnsions.

Not applicable

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.

mov
Esteemed Contributor III

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

whiteline
Honored Contributor II

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

Not applicable

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

thomas_skariah
Contributor III

Re: pivot table totals

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

Not applicable

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.

Thanks in advance,

Matt

Not applicable

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)

Community Browser