Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with pivot table sums - Blank cells are not carried through in totals

I have the problem illustrated in the screenshot below.  In the red ovals, any time there's a blank cell, the TOTAL column and rows do not get a value.  I suspected that it was because my field was a NULL value, so I did a SUMIF.  It did not work.

=NUM(SUM(IF(ISNULL(CEM_EAD),0,CEM_EAD)),MoneyFormat)

1.png

1 Reply
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi there,

You shouldn't need the IF statement at all, but the syntax if you do want to replace a null with a value (or zero) is Alt(CEM_EAD, 0).  Also, I would avoid putting the NUM function in your expression, strip this out and use the format options on the Number tab of the properties.  Hopefully this will resolve the issue.

If it does not it will be worth working out if there are values in those cells (perhaps spaces?) or whether there are simply no rows.  You can do this by selecting the NETG_FLAG of Y.  If BNK is still shown on the left then there is a rogue value in there, if not then there are no rows that share the values BNK and Y.

If there are values that need cleaning then I would suggest doing this in the load script.

Hope that helps,

Steve