topic SUM ALL/TOTAL, disregard show null settings in App Development

SUM ALL/TOTAL, disregard show null settings

Anonymous — Thu, 01 Sep 2016 18:10:23 GMT

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

sunny_talwar — Thu, 01 Sep 2016 18:20:02 GMT

This seems to be working for me

Re: SUM ALL/TOTAL, disregard show null settings

Anonymous — Thu, 01 Sep 2016 18:29:12 GMT

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

sunny_talwar — Thu, 01 Sep 2016 18:50:23 GMT

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

Anonymous — Thu, 01 Sep 2016 19:32:09 GMT

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)