topic Re: Null() Value Handling in Set Analysis in QlikView

Null() Value Handling in Set Analysis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Null() Value Handling in Set Analysis

sushil353 — Thu, 16 Mar 2017 04:14:45 GMT

Hi,

Can you elaborate your problem?

Re: Null() Value Handling in Set Analysis

muthukumar77 — Thu, 16 Mar 2017 06:01:03 GMT

Hi,

May be like this,

Eg:

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

Re: Null() Value Handling in Set Analysis

Anonymous — Thu, 16 Mar 2017 06:17:55 GMT

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

];

Re: Null() Value Handling in Set Analysis

avinashelite — Thu, 16 Mar 2017 06:23:47 GMT

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.

Re: Null() Value Handling in Set Analysis

Anonymous — Thu, 16 Mar 2017 08:33:19 GMT

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?

Re: Null() Value Handling in Set Analysis

Anonymous — Thu, 16 Mar 2017 08:44:32 GMT

Hi Muthu,

Doesnt work for my data table.

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

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

Re: Null() Value Handling in Set Analysis

avinashelite — Thu, 16 Mar 2017 09:41:35 GMT

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