# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for
Did you mean:
Specialist

## set analysis error

Hi Experts

I have the following set analysis

Sum({\$<[DATA_TYPE]={'SUMMARY'}, [BOOK3YRS]={'3'}>}LONG_TERM_DISALLOWED_LOSS)

in the table , Book3Yrs are all null, so i'd expect to get a zero, but the result seems to ignore the book3yrs.

Why is that and how do i correct the issue?

thanks!

1 Solution

Accepted Solutions
Specialist II

Hi @alexpanjhc , i tried modifyng your set analysis formula without good luke. Hope someone else have the answer.

But, i have 2 options for you that maybe works:

1.-

The problem is with the null values from the data, so you can make a little code in your load script, changing null to empty :

if(isnull(BOOK3YRS_1), '', BOOK3YRS_1) as BOOK3YRS

With that, your formula works fine.

2.-

Try with the normal IF :

Sum(if([DATA_TYPE] = 'SUMMARY' and [BOOK3YRS] = '3', LONG_TERM_DISALLOWED_LOSS))

with that your formula works fine too

QFabian
4 Replies
Specialist II

Hi @alexpanjhc , i tried modifyng your set analysis formula without good luke. Hope someone else have the answer.

But, i have 2 options for you that maybe works:

1.-

The problem is with the null values from the data, so you can make a little code in your load script, changing null to empty :

if(isnull(BOOK3YRS_1), '', BOOK3YRS_1) as BOOK3YRS

With that, your formula works fine.

2.-

Try with the normal IF :

Sum(if([DATA_TYPE] = 'SUMMARY' and [BOOK3YRS] = '3', LONG_TERM_DISALLOWED_LOSS))

with that your formula works fine too

QFabian
MVP

Your field has to have at least one value to get the desired value w.r.t the set analysis. Null is no-value, hence it doesn't check the filter condition (in symbol table). You can can try creating a blank value for null like below, and then it should work fine.

NullAsValue '*';

Set NullValue='';

MVP

It is as @tresesco  is saying. You need at least one non null value in your data set. One way to achieve this is to adda "empty" row containing a (any) value to the BOOK3YRS-column. In my script blow I created  blank ' ' value on my final row (All other fields are set to null() in this example).

``````set NullInterpret ='';  //Used to ensure that I get correct null values from my empty inline[] values
DATA_TYPE, 	BOOK3YRS,	LONG_TERM_DISALLOWED_LOSS
STRING, 	,			100
NUM, 		,			200
DATE, 		,			300
SUMMARY,	,			600
,  			" ",
];``````

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Specialist
Author

thank you!  i should have thought about it.

Tags
Community Browser