Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
crichter14
Creator II
Creator II

IF statement with set analysis excluding nulls

Oh, I have a mess.  Here's the calculation from the end user:

=If(
count({<
[criteria1]={'value1'},
[criteria2] = {'value2'},
[criteria3]={'value3'},
[criteria4]={'value4', 'value5', 'value6', 'value7'},
[critera5]={'value8'},
[critera6]-={'value9'}
>} distinct value10) > 1
and
Len(Concat({<
[criteria1]={'value1'},
[criteria2] = {'value2'},
[criteria3]={'value3'},
[criteria4]={'value4', 'value5', 'value6', 'value7'},

[critera5]={'value8'},
[critera6]-={'value9'}

>} DISTINCT value11, ', ')
) > 8, 'Multiple Status', null())

They want to exclude nulls from their straight table.

Obviously since this is a measure (not a dimension) the exclude null check box won't work.  I tried changing this to a dimension, except there's a Count in there so that doesn't seem to work.   Since it's a text value they are going for the include zero check box won't work either.

Pulling my hair out.  I'd like to keep this on the front end because the overall example is way too specific to include it in the data model.  Any geniuses out there with a suggestion or two?  Please save me from the creativity of others!

Labels (2)
2 Replies
anat
Master
Master

Can you try by removing else condition<null()>

Then enable suppress null values check box 

And suppress zero values

crichter14
Creator II
Creator II
Author

Thanks Anat!  After several meetings we agreed upon a flag that wasn't as narrow for them.  Between that and their filters we found a solution.