Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Straight Table with Expression using Set Analysis - Filtering problem

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.

1 Solution

Accepted Solutions
Not applicable

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

In that case use the following:

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

3 Replies
Not applicable

Straight Table with Expression using Set Analysis - Filtering problem

The quikest way is an intersection witht he current selection:

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

Regards,

Bert

Not applicable

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

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.

Not applicable

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

In that case use the following:

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

Community Browser