Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Null() Value Handling in Set Analysis

hey guys,

not sure if anyone resolved this null() set analysis handling in both text box and table logic to show up as grey coloring?

7 Replies
sushil353
Master II
Master II

Hi,

Can you elaborate your problem?

muthukumar77
Partner - Creator III
Partner - Creator III

Hi,

May be like this,

Eg:

Count({<field-={'*'}>}ID)

Muthukumar Pandiyan
Anonymous
Not applicable
Author

Hi Sushil,

Im trying make set analysis pick up null value in table 4 when Dept.CTR=A so that i can code it to grey color (vG.cGUI_Neutral) in the text box and pivot table.

Table4:

LOAD * INLINE [

    Dept.CTR,KPI_View, KPI_Type, KPI, Target, Actual, Var

    A, GO&T, Financial, Headcount,

    B, GO&T, Financial, Headcount,20,10,5

    C, GO&T, Financial, Headcount,30,10,3

    D, GO&T, Financial, Headcount,40,50,0

    E, GO&T, Financial, Headcount,20,30,5

    F, GO&T, Financial, Headcount,30,30,3

    G, GO&T, Financial, Headcount,40,20,1

    Total, GO&T, Financial, Headcount,150,3

];

avinashelite

if(len(trim(field_name))=0,grey(),RGB())

use this condition in the expression background color (click on + in the expression tab) and in the background color option in text object general tab.

Anonymous
Not applicable
Author

Thanks. This works for the text box on the left.

Can i make the null value show up in the pivot table Dept.CTR A?

Currently it is omitted due to null value and i have already unchecked the "suppress when value equals to null" in dimension tab. Is there any setting in expression tab or it has to be coded in script level?

Anonymous
Not applicable
Author

Hi Muthu,

Doesnt work for my data table.

I switch from sum to only so that it fit the len and trim function:kpi1.jpg

if(len(trim(only({<KPI_Type={"Financial"},KPI_View={"GO&T"},KPI={"Headcount"}>}Actual)))

=0,$(=vG.cGUI_Neutral),

(if(if([~KPI]='GO&T',

only({<KPI_Type={"Financial"},KPI_View={"GO&T"},KPI={"Headcount"}>}Target)

,only({<KPI_Type={"Financial"},KPI_View={"CBOT"},KPI={"Headcount"}>}Target))

>=

if([~KPI]='GO&T',

only({<KPI_Type={"Financial"},KPI_View={"GO&T"},KPI={"Headcount"}>}Actual)

,only({<KPI_Type={"Financial"},KPI_View={"CBOT"},KPI={"Headcount"}>}Actual))

, $(=vG.cGUI_Good), $(=vG.cGUI_Warning))))

avinashelite

Null values is in which column ? or your getting the null values after the calculation ?