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

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2013-11-26 06:07 PM

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,763 Views
0 Likes
1 Reply
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP
‎2013-11-26 07:37 PM

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

1,300 Views