11 Replies Latest reply: Jun 29, 2017 11:30 AM by pankaj thakur

# how to write expression

Hi all,

i need to show only those plant and suppliers whose terms are more then 1

plant      supplier         terms( expression i wrote is  count distinct terms)

xxx          aaa                  4

yyy            bbb                  3

zzz           ccc                     2

kkk           ddd                    1

hhh           eee                    1

but what i need to display is only those terms >1

plant      supplier         terms

xxx          aaa                  4

yyy            bbb                  3

zzz           ccc                     2

how to achieve this plz

thanks

• ###### Re: how to write expression

May be this?

Count({<terms = {"=Count(DISTINCT terms) > 1"}>} DISTINCT terms)

Or simply

Count({<terms = {">1"}>}DISTINCT terms)

• ###### Re: how to write expression

hi ,i am getting zero ,where did i went wrong

count({<SCOPE={'Yes'},MATL_TYPE={'HALB','ROH'},[Delivery Term]={">1"}>}distinct [Delivery Term])

• ###### Re: how to write expression

May be this ?

If(

count({<SCOPE={'Yes'},MATL_TYPE={'HALB','ROH'}>} distinct [Delivery Term]) >1,

count({<SCOPE={'Yes'},MATL_TYPE={'HALB','ROH'}>} distinct [Delivery Term])

)

• ###### Re: how to write expression

Hi i have used the above expression ,how to remove these rows

• ###### Re: how to write expression

A QlikView table object will show all dimension value sets for which at least one of your expressions results in a non-zero non-null value. If you want to suppress all expression values (whether they are zero or non-zero) based on a single outcome, you can do this by adding the check Tres suggested to ALL your expressions. In this case, add

If(

count({<SCOPE={'Yes'},MATL_TYPE={'HALB','ROH'}>} distinct [Delivery Term])>1,

...

)

to the expression in the Spend column.

Or use the set analysis technique suggested by Anil (which is more efficient)

• ###### Re: how to write expression

Check your SCOPE field value whether it is in upper format or lower

or try like MATL_TYPE={"HALB","ROH"}

• ###### Re: how to write expression

Try like:

Then enable null suppression as well.

• ###### Re: how to write expression

Count({<terms = {'>1'}>}DISTINCT terms)

• ###### Re: how to write expression

hi ,i am getting zero ,where did i went wrong

count({<SCOPE={'Yes'},MATL_TYPE={'HALB','ROH'},[Delivery Term]={">1"}>}distinct [Delivery Term])

• ###### Re: how to write expression

Hi,

PFA, Solution.

Regards,

Pankaj

• ###### Re: how to write expression

You may restrict the view in a table with an expression like

IF(terms > 1, terms)

terms might be replaced by an aggregation, in case need.

You may also restrict your data in the dimension, e.g.

IF(terms > 1, supplier)

Peter