Conditional filter in expression

[yvonne-c]

I am trying to create a dashboard of benchmarking bar charts.

I have a filter box to select the client, then I want all the charts to update based on the client selected.

A client may fall into one of either two or three groups.  Is it possible to select the correct group somehow in the chart expression?

Farm	FarmType
Client A	Organic
Client B	Organic
Client C	Conventional
Client D	Conventional

So if I want to compare results for those with the same 'farmtype'

If I select Client A  I want the chart to display only the clients  with Farmtype 'Organic'.

Is this possible?

Thanks

[Gysbert_Wassenaar]

There are several ways. The easiest is to also select Organic in the Farmtype field. Another way is to use a chart with a set analysis expression as measure. Suppose you now have sum(Revenue) as measure. You can change that to sum({<FarmType={'Organic'}>}Revenue) to make sure only the Organic farms are used to calculate the revenue.

[jonathandienst]

>>If I select Client A  I want the chart to display only the clients  with Farmtype 'Organic'.

The FarmType will be automatically filtered to Organic if you select farm A, but in your chart you would need to ignore the Farm selection to see all the Organic farms, like this:

     Sum({<Farm=>} Amount)

You could include FarmType as a dimension -- then this chart will work as an all details table by FarmType when no farm is selected, and will limit to one FarmType when one is selected.

[yvonne-c]

Thanks both, adding the farmtype as a dimension has helped a little as it has grouped all the organic/conventional clients together.  But what I would really like is for it only to show the one farmtype based on the selected farm - I am trying to benchmark the selected farm against all the others in that group.

If I put farmtype = organic in the chart expression, that's not going to work if I select a conventional farm.  And I don't want a list filter as then it would affect all the other charts on my sheet.

Any further ideas?

[jonathandienst]

Did you try simply overriding the Farm selection in the chart expression like I suggested?

[rwunderlich · Accepted solution]

I think you want to use the p() syntax for FarmType in your expressions. For example,

=Count({<Farm=,FarmType=p(FarmType)>} Farm)

-Rob

http://masterssummit.com

http://qlikviewcookbook.com