Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
kgordish
Contributor II
Contributor II

SUM Distinct, Avoid Nulls with Criteria for DataSubset

Hello,

Surprised Qlik formulas do not handle zero and null very well (or as I wish).
One situation where I miss Excel. 

Suppress Null checkbox is not working for me.

My current formula:

Sum(DISTINCT{$<[Metric Name]={'Proprietary Metric'},[Latest Month]={'Y'}>}[Metric_Value])

Data and chart in attached image. 

2019 and 2020 have genuine zero values (Orange Box).
2016, 2017, 2021-3 should be null on chart, but instead are displayed as zero. 

Searched for formulas, but not finding an exact of If IsNull that also has specifications for a subset of data where I am specifying two criteria (in my case a particular metric and month).

Any recommendations?

Thank you kindly.

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

How about this

If(Avg({$<[Metric Name] = {'Proprietary Metric'}, [Latest Month] = {'Y'}>} [Metric_Value]),

Sum(DISTINCT {$<[Metric Name] = {'Proprietary Metric'}, [Latest Month] = {'Y'}>} [Metric_Value])

)

 

View solution in original post

8 Replies
sunny_talwar

Can you try this

Sum(DISTINCT {$<[Metric Name] = {'Proprietary Metric'}, [Latest Month] = {'Y'}, [Metric_Value] *= {"*"}>} [Metric_Value])
kgordish
Contributor II
Contributor II
Author

Still not functioning properly. 
In need of another angle of attack. 


Tried changing Null Values to read Null using SET NullValue = 'Null'; NULLASVALUE Metric_Value;
Qlik is still showing null as zero. 

 

 

sunny_talwar

How about this

If(Avg({$<[Metric Name] = {'Proprietary Metric'}, [Latest Month] = {'Y'}>} [Metric_Value]),

Sum(DISTINCT {$<[Metric Name] = {'Proprietary Metric'}, [Latest Month] = {'Y'}>} [Metric_Value])

)

 

marcus_sommer

I'm not sure that Qlik behaved here really differently from Excel:

Update: screenshot is attached

What do you observed hasn't much to do with the loaded data (null, empty, missing) else with the fact that you create with your chart a new table - and in there context some values could become NULL. Within a tablebox they would be displayed as NULL respecitively the '-' as optical replace of it. But if you used an aggregation like sum() the result will be 0.

If you want to avoid it you will probably need to query for the result and then changing it, maybe with something like:

if(sum(value) = 0, null(), sum(value))

- Marcus

 

 

 

 

 

 

 

kgordish
Contributor II
Contributor II
Author

Tried suggested formulas.
Either nulls not showing, but also zero is not showing as well.

Non-ideal work around. 
I converted zero to 0.001 in dataset and changed number structure to Integer. 

Has to be a better solution. 

Wish I could send you a gift card for this "teaching moment". 

Attached Qlik File. 

sunny_talwar

What is a difference between Attempt 2 and Attempt 3? They look identical except the formatting for the symbol location

image.png

kgordish
Contributor II
Contributor II
Author

Sunny Your formula was helpful. It helped solve my requirement. 

I must have copied the same object twice by accident.  Accepted your previous solution. 

Thank you. 

sunny_talwar

Very cool.