Including null values from one dimension in Qlik sense

[Romstar111]

Hi there, 

I was wondering if anyone in this network could help. I am trying to include null values in my set analysis without luck. Even trying with count if brought no joy. I have checked other similar examples but it seems Qilk sense is tricky with null values. The data is imported from Salesforce and the Salesforce report is accurate but not in Qlik sense.

I have tried the following:

Count({<

Status={
'Application In Progress',
'Application Submitted',
'Offer Conditional',
'Offer Made',
'Offer unconditional',
'Offer Accepted',
'Active',
'Final Preparation',
'Completed',
'Assessment Scheduled',
'Recommended for QTS',
'Deferred',
'Rejected',
'Withdrawn',
"*"}
>}Id)

and

count(if(match(
Stage_Status__c ,
'Application In Progress',
'Application Submitted',
'Offer Conditional',
'Offer Made',
'Offer unconditional',
'Offer Accepted',
'Active',
'Final Preparation',
'Completed',
'Assessment Scheduled',
'Recommended for QTS',
'Deferred',
'Rejected',
'Withdrawn',
''),
Opportunity_Id))

[dplr-rn]

try below expression

Count({<

Status={
'Application In Progress',
'Application Submitted',
'Offer Conditional',
'Offer Made',
'Offer unconditional',
'Offer Accepted',
'Active',
'Final Preparation',
'Completed',
'Assessment Scheduled',
'Recommended for QTS',
'Deferred',
'Rejected',
'Withdrawn'}
>
+ <Id={"=isnull(Status)"}>
}Id)

i am assuming you want these statuses or null

[Channa]

if (isnull(Status) or pick(match('Application In Progress',
'Application Submitted',
'Offer Conditional',
'Offer Made',
'Offer unconditional',
'Offer Accepted',
'Active',
'Final Preparation',
'Completed',
'Assessment Scheduled',
'Recommended for QTS',
'Deferred',
'Rejected',
'Withdrawn'),count(ststus)

[Romstar111]

Hey  Dilipranjith

Thank you for your help but it is not working. Fore some reason it just add an extra layer of the null values on top an extra 15 in the count value.

[Romstar111]

Hi Channa, This is not working unfortunately. Not returning a value.

[Romstar111 · Accepted solution]

Solved!

I had to force qlik sense to accept null values from the load script.

e.g 

NullAsValue A,B;

Set NullValue = 'NULL'; 

More resources on this from the following: https://help.qlik.com/en-US/sense/April2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegular...

Thanks guys for your help. 🙂