Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
The quikest way is an intersection witht he current selection:
=count( { $*$ <[Status] = {'Closed'}> } [Date] )
Regards,
Bert
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.
In that case use the following:
=sum(if(Status='Closed'),1,0)