Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
crissaegrim
New Contributor III

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.

4 Replies

Re: SUM ALL/TOTAL, disregard show null settings

This seems to be working for me

Capture.PNG

crissaegrim
New Contributor III

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)

1.png2.png

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?

crissaegrim
New Contributor III

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)

Community Browser