topic Straight Table with Expression using Set Analysis - Filtering problem in QlikView

Straight Table with Expression using Set Analysis - Filtering problem

Wed, 24 Aug 2011 14:46:06 GMT

Consider a straight table chart where you have a few simple dimensions, then a single Expression. The Expression is the Count of records, but using Set Analysis to count only records meeting a specific criteria.

For example, if data includes columns: Date, ID, Status, Type

And chart dimensions are: ID, Status, Type

And chart expression is: =count( { $ <[Status] = {'Closed'}> } [Date] )

So this works fine, but when user filters the data to show only where Status is 'Open' (or something other than 'Closed'), the chart still displays these Closed items. Because the expression evaluates to a non-zero value, and the Set Modifier essentially replaces the selection for [Status], it makes it appear that the filter on Status is not working.

So how can I have the chart display the same data, but when filtering on Status = 'Open', only Open records should show, and of course the Count would be 0 (since it's counting only where Status is 'Closed').

See example attached.

Straight Table with Expression using Set Analysis - Filtering problem

Wed, 24 Aug 2011 15:00:35 GMT

The quikest way is an intersection witht he current selection:

=count( { $*$ <[Status] = {'Closed'}> } [Date] )

Regards,

Bert

Re: Straight Table with Expression using Set Analysis - Filtering problem

Wed, 24 Aug 2011 15:07:15 GMT

Thank you Bert, but using an Intersect like that, even when I allow Zero-Values (via Presentation tab of Chart options) doesn't give me the results I want. If I select a status such as 'Open', the Chart just shows now rows at all, whereas I want to see the 'Open' rows (and the count be displayed as 0).

When filtering on Status = 'Pending' ...

- An Intersect using =count( { $ * $<[Status] = {'Closed'}> } [Date] ) shows no rows

- A Union using =count( { $ + $<[Status] = {'Closed'}> } [Date] ) shows our filtered rows, but always shows the 'Closed' rows, which is not what we want.

Re: Straight Table with Expression using Set Analysis - Filtering problem

Fri, 26 Aug 2011 07:06:13 GMT

In that case use the following:

=sum(if(Status='Closed'),1,0)