[QlikView] how to filter null in formula set analysis

[chenpeng]

hi everyone,

There are tow tables linked by key in the QVW file:

the data table and the chart showing as following:

I want to filter the dim table field [Movement_Cause_Code_Quartely_CP] not equal 1300.

however, the matrix can't calculate the result when the dim field no value matched (red rectangle cells).

I need how to modify my expression in EXP1 to let it work?

Thanks.

QlikView 

[Chanty4u]

Try this 

LOAD *,

    If(IsNull([Movement_Cause_Code_Quartely_CP]), 'Null', [Movement_Cause_Code_Quartely_CP]) as MC_Code_Clean

Resident DimTable;

And in expression 

Sum({<MC_Code_Clean -= {'1300'}>} YourMeasureField)

[QFabian]

Hi @chenpeng , try this mixed expression between some tradicional IF and Set Analysis

EXP1.1 :

sum(if([Movement_Cause_Code_Quarterly_CP] <> 1300,
{<Model_CD = {'Trad'}, Method -= {'PAA'} >} Value))

[Lech_Miszkiewicz · Accepted solution]

You cannot apply selections on nulls in Qlik (same applies to set analysis). 

The workaround would be:

• to sum everything minus sum of what you want to exclude
  • Sum(Value)-Sum({<[Movement_Cause_Code_Quartely_CP] ={1300}>} Value)

[chenpeng]

Hi Miszk,

Thanks for you confirmation! The approach you mentioned it work properly. I already find this solution before I post the question, I just want to find there is any simple/clear expression can meet the requirement.

have a nice day!

[Lech_Miszkiewicz]

I dont think there is better solution as your data is just not associated at this stage. For nicer solution you would need to make changes to your data model, join both tabels and likely populate nulls with selectable values.

cheers