4 Replies Latest reply: Sep 1, 2016 3:32 PM by KHOA NGUYEN

# SUM ALL/TOTAL, disregard show null settings

I have a calculation that depends on getting the correct value of SUM(TOTAL <Dimension01> Field01).  I have a pivot table that displays `Dimension02` that can have NULL values.  I want to hide the NULL values, but still get the correct sum of `Field01`.  How do I achieve this?

SUM(TOTAL <Dimension1>

SUM ALL TOTAL <Dimension1>

SUM({1}

SUM(ALL

SUM(TOTAL <Dimension1> {1}

etc...

My sum is very much dependent on the Show Null Values setting.  I want to disregard it.

• ###### Re: SUM ALL/TOTAL, disregard show null settings

This seems to be working for me

• ###### Re: SUM ALL/TOTAL, disregard show null settings

Not for me.  Please see screenshots.  My formula:  Sum(Total <Account> {1<Account = {"=Count({<Sector = {'Hedge - Credit','Hedge - Options'}>} Investment) > 0"}>} MktValue_with_AI)

• ###### Re: SUM ALL/TOTAL, disregard show null settings

Would you be able to create a sample where you can replicate the issue for us to check?

• ###### Re: SUM ALL/TOTAL, disregard show null settings

I'm using Qlik Sense.  I'm attaching the qvf export of the app.

Here's the relevant data:

Set NullInterpret = '';

[Data1]:

LOAD Investment, Account, HEDGE_TYPE, Sector, MarketValue INLINE [

Investment, Account, HEDGE_TYPE, Sector, MarketValue

1, Account01, CDX, Hedge,100

2, Account01, CDX, Hedge,100

3, Account02, , Hedge,500

4, Account01, , Home,100

5, Account02, CMBX, Hedge,500

6, Account01, , Hedge,500

7, Account02, , Hedge,500

8, Account01, , Hedge,9999

9, Account0333, Cars, ,500

];

Formula:

Sum(Total <Account> {<Account = {"=Count({<Sector = {'Hedge'}>} Investment) > 0"}>} MarketValue)