topic Re: how to write expression in QlikView

how to write expression

kunkumnaveen — Thu, 29 Jun 2017 12:22:34 GMT

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

Anil_Babu_Samineni — Thu, 29 Jun 2017 12:26:34 GMT

May be this?

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

Or simply

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

Re: how to write expression

tresB — Thu, 29 Jun 2017 12:27:26 GMT

Try like:

If( your count expression >1, your count expression)

Then enable null suppression as well.

Re: how to write expression

prma7799 — Thu, 29 Jun 2017 12:28:03 GMT

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

Re: how to write expression

prieper — Thu, 29 Jun 2017 12:33:53 GMT

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

Re: how to write expression

kunkumnaveen — Thu, 29 Jun 2017 12:40:50 GMT

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

kunkumnaveen — Thu, 29 Jun 2017 12:41:35 GMT

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

tresB — Thu, 29 Jun 2017 12:46:16 GMT

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

prma7799 — Thu, 29 Jun 2017 12:47:51 GMT

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

kunkumnaveen — Thu, 29 Jun 2017 13:18:26 GMT

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

Re: how to write expression

Peter_Cammaert — Thu, 29 Jun 2017 15:18:49 GMT

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

passionate — Thu, 29 Jun 2017 15:30:16 GMT

Hi,

PFA, Solution.

Regards,

Pankaj