Qlik Community

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 
Search instead for 
Did you mean: 
alexpanjhc
Specialist
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
QFabian
Specialist II
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

View solution in original post

4 Replies
QFabian
Specialist II
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

View solution in original post

tresesco
MVP
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='';

Vegar
MVP
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
LOAD * inline [
	DATA_TYPE, 	BOOK3YRS,	LONG_TERM_DISALLOWED_LOSS
	STRING, 	,			100
	NUM, 		,			200
	DATE, 		,			300
	SUMMARY,	,			600
	,  			" ",
];

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
alexpanjhc
Specialist
Specialist
Author

thank you!  i should have thought about it.