topic Help with Expression in QlikView

Help with Expression

Anonymous — Tue, 27 Aug 2013 21:38:26 GMT

I have a field called STATUS , in which the values are OPEN and CLOSED.

Another Field POSITION : ACTIVE

I need to create a Bar Chart that shows only OPEN and ACTIVE values by Date , and the another Table which shows ALL values

Bar Chart :

Dimension : Date

Expression : Count(distinct(if(Status = 'OPEN' and Position = 'ACTIVE',id))

Table : Staight Table with all columns

The problem i am facing is , when a user selects a Date in Bar Chart , he can see Open values in Bar Chart and Both Open and Closed Values in Table , which is fine , but when a user selects CLOSED status from Table , the Bar chart is messed up.

I tried changing the dimension to if( STATUS='OPEN',Date) which says No data to display when CLOSED is selected in Table which is how i want , but by doing this when user selects Date from Bar chart , the Table is displayed only for OPEN Values because i have enabled in dimension to pick only STATUS = OPEN, which is NOT what i want.

Can someone please let me know how the Bar chart can show data only for OPEN Staus values.

Thanks

Re: Help with Expression

Oleg_Troyansky — Tue, 27 Aug 2013 21:59:05 GMT

You need to replace your IF formula with Set Analysis and override user selections on the fields STATUS and POSITION:

Count( {<Status = {OPEN}, Position = {ACTIVE} >} distinct id)

This way, your expression will remain correct even if the user selects Status="CLOSED"

Re: Help with Expression

Anonymous — Wed, 28 Aug 2013 00:48:24 GMT

The expression i am using currently is calculating percentage , for simplicity i have given in if statement .. below is the exact expression i am using which does not seem to work

Count((<STATUS = {OPEN},POSITION={ACTIVE}>}distinct( ID )/

Count({<STATUS = {OPEN},POSITION={ACTIVE}>}TOTAL<Date>distinct ( id))

Re: Help with Expression

Sokkorn — Wed, 28 Aug 2013 03:41:41 GMT

Hi Ramya,

The expression should be this:

Count(<STATUS = {'OPEN'},POSITION={'ACTIVE'}>} Distinct ID )/

Count({<STATUS = {'OPEN'},POSITION={'ACTIVE'}>} TOTAL<Date> Distinct ID)

Let me know if not work.

Regards,

Sokkorn