topic Re: making a value as null in QlikView

making a value as null

Thu, 18 Sep 2014 08:57:10 GMT

Hi,

I have a scenario where I get a Null value in two consecutive columns then the complete row should be Null value in the below png file but there is value that shows the value 223 i should make the value null and the next column value also as null

Re: making a value as null

MK_QSL — Thu, 18 Sep 2014 08:59:00 GMT

Can you please share sample data or sample apps?

Re: making a value as null

anbu1984 — Thu, 18 Sep 2014 09:03:10 GMT

Try like this

Create labels RPH_Sel_PY and RPH_Growth for expressions before RPH Benchmark expr

Change RPH Benchmark expr to below

If(IsNull(RPH_Sel_PY) And IsNull(RPH_Growth),,RPH_Benchmark_expr)

Re: making a value as null

Thu, 18 Sep 2014 09:08:08 GMT

=if(Dimensionality()=0,sum({<$(vLimit_Current_BM), $(vParam_BM_Set_Geography_Seletions), $(vParam_BM_Set_Function_Selections)>}[BM_Sum_NSR]*$(vParam_BM_RateType))/sum({<$(vLimit_Current_BM), $(vParam_BM_Set_Geography_Seletions), $(vParam_BM_Set_Function_Selections)>}[BM_Revenue_Total_Hours]),

if($(vParam_BM_Function_Level)=$(vParam_Function_Level),sum({<$(vLimit_Current_BM), $(vParam_BM_Set_Geography_Seletions)>}[BM_Sum_NSR]*$(vParam_BM_RateType))

/sum({<$(vLimit_Current_BM),$(vParam_BM_Set_Geography_Seletions)>}[BM_Revenue_Total_Hours]))

)

I have an expression like this now i need to insert that expression regarding null could you explain me in detail

Re: making a value as null

jagan — Thu, 18 Sep 2014 09:12:43 GMT

Hi Bhanu,

Let us assume you have 3 expressions

Exp1: Sum(Measure1)

Exp2: Sum(Measure2)

Exp3: Sum(Measure3)

If you want to remove the row if Exp1 and Exp2 are null then try like this

Exp1: Sum(Measure1)

Exp2: Sum(Measure2)

Exp3:

If( Alt(Sum(Measure1), 'N/A') <> 'N/A' AND Alt(Sum(Measure1), 'N/A') <> 'N/A', Sum(Measure3))

If( Alt([Exp1], 'N/A') <> 'N/A' AND Alt([Exp2], 'N/A') <> 'N/A', Sum(Measure3))

And make sure in Presentation tab you have selected Supress Null option.

Hope this helps you.

Regards,

jagan.

Re: making a value as null

anbu1984 — Thu, 18 Sep 2014 09:13:19 GMT

First create labels(RPH_Sel_PY and RPH_Growth)for previous two expr

Here is expr for RPH Benchmark

-------------------------------------------------------------------------------

If(IsNull(RPH_Sel_PY) And IsNull(RPH_Growth),,

if(Dimensionality()=0,sum({<$(vLimit_Current_BM), $(vParam_BM_Set_Geography_Seletions), $(vParam_BM_Set_Function_Selections)>}[BM_Sum_NSR]*$(vParam_BM_RateType))/sum({<$(vLimit_Current_BM), $(vParam_BM_Set_Geography_Seletions), $(vParam_BM_Set_Function_Selections)>}[BM_Revenue_Total_Hours]),

if($(vParam_BM_Function_Level)=$(vParam_Function_Level),sum({<$(vLimit_Current_BM), $(vParam_BM_Set_Geography_Seletions)>}[BM_Sum_NSR]*$(vParam_BM_RateType))

/sum({<$(vLimit_Current_BM),$(vParam_BM_Set_Geography_Seletions)>}[BM_Revenue_Total_Hours]))

)

-------------------------------------------------------------------------------

If RPH_Sel_PY expr gives null and RPH_Growth expr gives null value,

then

set null to RPH Benchmark

else

your current RPH Benchmark