topic and syntax in an expression in QlikView

and syntax in an expression

Wed, 11 Dec 2013 08:07:18 GMT

Hi All,

Could you please kindly assist me with the proper use of the "and" logical function in an expression. For example, I have tried using it as follows: Count(If(Year='2013'and [Estimate-Kg]>='0' and [Sales-Kg]<='1',[Grower No.],0)), this doesn't quite work. Is there also some sort of a "syntax" guide available?

Thank you

Herbiec 09

Re: and syntax in an expression

Wed, 11 Dec 2013 08:22:32 GMT

Please rewrite the expression as,

If(Year='2013'and [Estimate-Kg]>='0' and [Sales-Kg]<='1',Count([Grower No.]),0)

Regards,

Prabhu

Re: and syntax in an expression

hic — Wed, 11 Dec 2013 08:35:54 GMT

Your expression will return a number different from what you want. You use

Count(If(Year='2013'and [Estimate-Kg]>='0' and [Sales-Kg]<='1',[Grower No.],0))

You have four fields inside the aggregation, fields that probably reside in different tables. The logic is that QlikView first creates the join between these tables, and then performs the count on this temporary table. I.e. the number of rows will be much larger than you want.

Prabhu's expression will work - but only if "Year", "Estimate-Kg" and "Sales-Kg" exist as dimensions in the graph. Otherwise these will be evaluated to NULL. See more on http://community.qlik.com/blogs/qlikviewdesignblog/2013/08/06/it-s-all-aggregations

No, the proper way to do this, is to use Set Analysis:

Count({$<Year={'2013'}, [Estimate-Kg]={">=0"}, [Sales-Kg]={"<=1"}>} [Grower No.] )

HIC

Re: and syntax in an expression

Wed, 11 Dec 2013 08:41:23 GMT

Thanks Prabhu,

Unfortunately it's returning a zero count. I have manually checked the data, and I have instances where I have estimated kg's, but no actual sales, i.e. sales-kg = 0,

Any thoughts?

Herbiec09

Re: and syntax in an expression

Wed, 11 Dec 2013 08:45:16 GMT

As said by Henric .. Unless you have "Year", "Estimate-Kg" and "Sales-Kg as dimensions the above expression wont work.

It is better to use Set Analysis.

Re: and syntax in an expression

Wed, 11 Dec 2013 09:21:09 GMT

Spot On, many thanks Henric, this works perfectly. Really appreciate your help.

Re: and syntax in an expression

Wed, 11 Dec 2013 09:22:25 GMT

Many thanks Prabhu,

Yes u are right, that has finally worked. Appreciate you taking the time to look at it.