topic Re: not expression in if expression in QlikView

not expression in if expression

thanhphongle — Tue, 14 Jun 2016 12:35:14 GMT

This is my database

Product	Status
Notebook	Broken
Notebook	Broken
Notebook	Broken
Notebook	Stolen
Notebook	Borrowed
Cellphone	Broken
Cellphone	Broken
Cellphone	Broken
Cellphone	Sold
Cellphone	In used

I want QV to return in a Textbox

Broken Products = 6

Not Broken Products = 4

I used this as expression

if(Status='Broken', count(Product))

if(Status=<>'Broken', count(Product))

Unfortunetely it is not working. Is there a solution with if expression and set analysis?

Re: not expression in if expression

sunny_talwar — Tue, 14 Jun 2016 12:37:56 GMT

Try this:

1) Count({<Status = {'Broken'}>}Product)

2) Count({<Status -= {'Broken'}>}Product) or Count({<Status ={'*'} - {'Broken'}>}Product)

Re: not expression in if expression

trdandamudi — Tue, 14 Jun 2016 12:38:10 GMT

May be as below:

=Count( {<Status={'Broken'}>}Product)

=Count( {<Status - ={'Broken'}>}Product)

Re: not expression in if expression

Digvijay_Singh — Tue, 14 Jun 2016 12:39:25 GMT

Try this -

='Broken Product = ' & Count({<Status={'Broken'}>}Product) & chr(13) &

'Not Broken Products = ' & Count({<Status-={'Broken'}>}Product)

Re: not expression in if expression

mightyqlikers — Tue, 14 Jun 2016 12:39:59 GMT

PFA.

$@M.

Re: not expression in if expression

sunny_talwar — Tue, 14 Jun 2016 12:40:53 GMT

Sample attached

Re: not expression in if expression

thanhphongle — Tue, 14 Jun 2016 12:43:18 GMT

Thank you for the solution. Is there a solution with an IF- Expression?

Re: not expression in if expression

sunny_talwar — Tue, 14 Jun 2016 12:44:33 GMT

Like this:

1) Count(If(Status = 'Broken', Product))

2) Count(If(Status <> 'Broken', Product))

Re: not expression in if expression

sunny_talwar — Tue, 14 Jun 2016 12:46:08 GMT

Updated sample. But I am curious to know why do you want to use if rather than set analysis here? Any specific reason here?

Re: not expression in if expression

Digvijay_Singh — Tue, 14 Jun 2016 12:46:17 GMT

Set expression will give better performance as it works with reduced set of records

Re: not expression in if expression

sunny_talwar — Tue, 14 Jun 2016 12:48:01 GMT

I agree and if you need the set expression to be filtered based on selection in Status field. You can try these expressions:

1) Count({<Status *= {'Broken'}>}Product)

2) Count({<Status -= {'Broken'}>}Product)

Re: not expression in if expression

thanhphongle — Tue, 14 Jun 2016 12:51:36 GMT

My Set Analysis skills are not that good. But I'm still learning it. Thank you for your advices. I see my mistake with the if expression. I didnt expect that i have to use the count espression first and then the if expression. Thank you very much guys!

Re: not expression in if expression

sunny_talwar — Tue, 14 Jun 2016 13:00:26 GMT

Basic set analysis is very easy and efficient way to restrict things. Lets go over the few expressions that I and others have used above.

1) Count({<Status = {'Broken'}>}Product)

Regardless of selection in Status, always show count of product where status = Broken.

2) Count({<Status *= {'Broken'}>}Product) -> Equailant of Count(If(Status = 'Broken', Product))

Show count of product where status is equal to Broken. But this is going to filter out any broken status if they are not part of the selection. So for instance if you select Sold in your example above, you will see 0 instead of 6.

3) Count({<Status ={'*'} - {'Broken'}>}Product)

Regardless of selection in status, always show couunt of products where status is not equal to broken

4) Count({<Status -= {'Broken'}>}Product) -> Equailant of Count(If(Status <> 'Broken', Product))

Show count of product where status is not equal to Broken. But this is going to filter out any non-broken status if they are not part of the selection. So for instance if you select Sold in your example above, you will see 1 instead of 4. If you select Broken, you will see 0.

I hope this will help clarify some of your set analysis doubts. For more information read the attached document. The document isn't perfect, but can help you learn a lot of things.