topic Including null values from one dimension in Qlik sense in App Development

Including null values from one dimension in Qlik sense

Romstar111 — Sat, 16 Nov 2024 20:40:55 GMT

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))

Re: Including null values from one dimension in Qlik sense

dplr-rn — Wed, 12 Jun 2019 16:58:22 GMT

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

Re: Including null values from one dimension in Qlik sense

Channa — Thu, 13 Jun 2019 02:21:32 GMT

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)

Re: Including null values from one dimension in Qlik sense

Romstar111 — Thu, 13 Jun 2019 09:12:15 GMT

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.

Re: Including null values from one dimension in Qlik sense

Romstar111 — Thu, 13 Jun 2019 09:13:16 GMT

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

Re: Including null values from one dimension in Qlik sense

Romstar111 — Thu, 13 Jun 2019 15:35:40 GMT

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/ScriptRegularStatements/NullAsValue.htm

Thanks guys for your help. 🙂