topic Re: Set expression question in QlikView

Set expression question

Wed, 25 Nov 2020 16:16:04 GMT

Hi there,

I am a relative newbie to QlikView and am struggling with a set expression. I have a chart with 4 data fields, which are traits on products that are bought and sold by us. Based on those fields, I want to count the number of total transactions, as well as purchases and sales.

Each transaction has a trade number (trade_num) and buy/sell indicator (bs_ind), neither of which are one one of the 4 trait fields.

In the end, I am looking set of traits where there are multiple transaction for the set of traits, including at least one buy AND one sale.

For Total Records, this expression seems to work fine:

=if(count({$} DISTINCT trade_num)>=2, count({$}DISTINCT trade_num))

However, when I want to narrow down to records where I have both at least one buy AND one sell I am having trouble. I cannot even get the buy count correct. Why is this expression not working?

=if(count({$} DISTINCT trade_num_alt)>2, count({$<[bs_ind]={'B'}>}DISTINCT trade_num_alt),0)

Thank you in advance for any help!

Re: Set expression question

sunny_talwar — Wed, 09 Mar 2016 21:39:49 GMT

Try creating a new expression -> count({$} DISTINCT trade_num_alt) and check if it is ever greater than 2? if it isn't then the above expression will always be equal to 0. If it does, then try count({$<[bs_ind]={'B'}>}DISTINCT trade_num_alt) and see if this is getting to be 0

Re: Set expression question

engishfaque — Thu, 10 Mar 2016 03:52:53 GMT

Dear Lyon,

As you mentioned your given below expression is working fine, it means trade_num have count more than or equal to 2 therefore, you are getting result.

=if(count({$} DISTINCT trade_num)>=2, count({$}DISTINCT trade_num))

Please make sure in your expression no. 2 field values, either field trade_num_alt has more than 2 counts.

Please check

Kind regards,

Ishfaque Ahmed