Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

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
Anonymous
Not applicable

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

View solution in original post

9 Replies
paulyeo11
Master
Master
Author

My QVF

Anonymous
Not applicable

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
Master
Master
Author

Hi Bill

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

Paul

Anonymous
Not applicable

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

paulyeo11
Master
Master
Author

Hi Bill

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

RELATED COY.png

Anonymous
Not applicable

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

paulyeo11
Master
Master
Author

Hi Bill

You are right .

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

Paul

Anonymous
Not applicable

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
Master
Master
Author

Hi Bill

Thank a Lot.

Paul