Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

paulyeo11
Valued Contributor II

Expression surpress null value work in QV but not work in QS.

Hi All


I have below expression from Sunny working fine :-


Only({<SEGMENT -= {'Related Coy'}>} 'S$ ') &
Num(
sum({<SEGMENT -= {'Related Coy'}>}
If( US_Cust='USD', 1.3,
If( US_Cust='RP', 0.0001,
if( US_Cust='EU',1.5, 1)))* days3)

+ sum({<SEGMENT -= {'Related Coy'}>}
If ( US_Cust='USD', 1.3,
If ( US_Cust='RP', 0.0001,
if ( US_Cust='EU',1.5, 1))) * days6)

+ sum({<SEGMENT -= {'Related Coy'}>}
If ( US_Cust='USD', 1.3,
If ( US_Cust='RP', 0.0001,
if ( US_Cust='EU',1.5, 1))) * days9)

+ sum({<SEGMENT -= {'Related Coy'}>}
If ( US_Cust='USD', 1.3,
If ( US_Cust='RP', 0.0001,
if ( US_Cust='EU',1.5, 1)))*above12)

+ sum({<SEGMENT -= {'Related Coy'}>}
If ( US_Cust='USD', 1.3,
If ( US_Cust='RP', 0.0001,
if ( US_Cust='EU',1.5, 1))) * curren)
,'###,##0.00')

But when i use the same expression in QS , i am unable to surpress the null row. ( it work fine in QV )

Paul

1 Solution

Accepted Solutions

Re: Expression surpress null value work in QV but not work in QS.

I suspect all your expressions have to be zero for the rows to be suppressed see attached

For Company Pay try this

if (

sum({<SEGMENT -= {'Related Coy'}>}

If( US_Cust='USD', 1.3,

If( US_Cust='RP', 0.0001,

if( US_Cust='EU',1.5, 1)))* days3)

+ sum({<SEGMENT -= {'Related Coy'}>}

If ( US_Cust='USD', 1.3,

If ( US_Cust='RP', 0.0001,

if ( US_Cust='EU',1.5, 1))) * days6)

+ sum({<SEGMENT -= {'Related Coy'}>}

If ( US_Cust='USD', 1.3,

If ( US_Cust='RP', 0.0001,

if ( US_Cust='EU',1.5, 1))) * days9)

+ sum({<SEGMENT -= {'Related Coy'}>}

If ( US_Cust='USD', 1.3,

If ( US_Cust='RP', 0.0001,

if ( US_Cust='EU',1.5, 1)))*above12)

+ sum({<SEGMENT -= {'Related Coy'}>}

If ( US_Cust='USD', 1.3,

If ( US_Cust='RP', 0.0001,

if ( US_Cust='EU',1.5, 1))) * curren)

> 0 ,

if((Sum(curren)+Sum(days3)+Sum(days6)+Sum(days9)+Sum(above12))<>0, COMPANY_PAY, null() ) , null() )

and similar for TERMES

9 Replies
paulyeo11
Valued Contributor II

Re: Expression surpress null value work in QV but not work in QS.

My QVF

Re: Expression surpress null value work in QV but not work in QS.

Your expressions for Company Pay and TERMS are setting a value of '' if the if condition is not met.  Try setting them both them both to null() instead :

     if((Sum(curren)+Sum(days3)+Sum(days6)+Sum(days9)+Sum(above12))<>0, COMPANY_PAY, null() )

     if((Sum(curren)+Sum(days3)+Sum(days6)+Sum(days9)+Sum(above12))<>0, TERMS, null())

paulyeo11
Valued Contributor II

Re: Expression surpress null value work in QV but not work in QS.

Hi Bill

I set according to your instruction . But the row with null value from SEGMENT = Related Coy , still display there.

Paul

Re: Expression surpress null value work in QV but not work in QS.

I can't find the row you are referring to.  Could you share a screen shot of it ?

paulyeo11
Valued Contributor II

Re: Expression surpress null value work in QV but not work in QS.

Hi Bill

notice that all the row shown are SEGMENT = Related Coy , The amount is null , how to surpress these row ?

RELATED COY.png

Re: Expression surpress null value work in QV but not work in QS.

When you null do you mean nil, as in the value of 0.00 for Total W/O Related coy ?

paulyeo11
Valued Contributor II

Re: Expression surpress null value work in QV but not work in QS.

Hi Bill

You are right .

I am sorry , i still said zero value not nul .

Paul

Re: Expression surpress null value work in QV but not work in QS.

I suspect all your expressions have to be zero for the rows to be suppressed see attached

For Company Pay try this

if (

sum({<SEGMENT -= {'Related Coy'}>}

If( US_Cust='USD', 1.3,

If( US_Cust='RP', 0.0001,

if( US_Cust='EU',1.5, 1)))* days3)

+ sum({<SEGMENT -= {'Related Coy'}>}

If ( US_Cust='USD', 1.3,

If ( US_Cust='RP', 0.0001,

if ( US_Cust='EU',1.5, 1))) * days6)

+ sum({<SEGMENT -= {'Related Coy'}>}

If ( US_Cust='USD', 1.3,

If ( US_Cust='RP', 0.0001,

if ( US_Cust='EU',1.5, 1))) * days9)

+ sum({<SEGMENT -= {'Related Coy'}>}

If ( US_Cust='USD', 1.3,

If ( US_Cust='RP', 0.0001,

if ( US_Cust='EU',1.5, 1)))*above12)

+ sum({<SEGMENT -= {'Related Coy'}>}

If ( US_Cust='USD', 1.3,

If ( US_Cust='RP', 0.0001,

if ( US_Cust='EU',1.5, 1))) * curren)

> 0 ,

if((Sum(curren)+Sum(days3)+Sum(days6)+Sum(days9)+Sum(above12))<>0, COMPANY_PAY, null() ) , null() )

and similar for TERMES

paulyeo11
Valued Contributor II

Re: Expression surpress null value work in QV but not work in QS.

Hi Bill

Thank a Lot.

Paul