Null() Value Handling in Set Analysis

[Anonymous]

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?

[sushil353]

Hi,

Can you elaborate your problem?

[muthukumar77]

Hi,

May be like this,

Eg:

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

[Anonymous]

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]

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]

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

[avinashelite]

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