9 Replies Latest reply: Jul 28, 2016 3:23 PM by Yeo Poh sai

# 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

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

• ###### 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 ?

• ###### 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 ?

• ###### 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 ?

• ###### 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

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

Hi Bill

Thank a Lot.

Paul