Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

making a value as null

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

1 Solution

Accepted Solutions
anbu1984
Master III
Master III

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)

View solution in original post

5 Replies
MK_QSL
MVP
MVP

Can you please share  sample data or sample apps?

anbu1984
Master III
Master III

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)

Not applicable
Author

=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

jagan
Partner - Champion III
Partner - Champion III

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

OR

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.

anbu1984
Master III
Master III

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