topic Re: Remove expression based on filter selection in QlikView

Remove expression based on filter selection

Anonymous — Thu, 03 Jan 2013 09:27:36 GMT

I am in the process of trying to build a chart which shows values for a customer against the market average. Due to data confidentiality, the market average can only be filtered by one dimension (in this case "Class Of Business"), so what I would like to be able to do is hide the market average expression if the data is filtered on any dimension other than "Class Of Business".

Unfortunately I can't think of a way to achieve this. Has anyone got any ideas?

Hopefully I've been clear in what I want to achieve.

Re: Remove expression based on filter selection

khadeer — Thu, 03 Jan 2013 09:33:13 GMT

Hi,

Try aggr("ur expression", "dimension")

Aggr(market average, Class of Business)

Hope it will help u.

Re: Remove expression based on filter selection

Anonymous — Thu, 03 Jan 2013 09:40:53 GMT

Thanks, but unfortunately that hasn't worked.

What I didn't mention was that this is a trend chart, with the expressions spread over a time period (in this case year and month). With the Aggr function added, I lose market average values for certain months, and filtering the data on anything other than Class Of Business isn't hiding the expression.

Re: Remove expression based on filter selection

Thu, 03 Jan 2013 10:21:46 GMT

Hello,

In your expression, you can use the set analysis like this :

{1<[Class Of Business]=$::[Class Of Business]>}

For example :

sum({1<[Class Of Business]=$::[Class Of Business]>} [Another field])

Amand Dupretz

Re: Remove expression based on filter selection

Anonymous — Thu, 03 Jan 2013 10:56:28 GMT

Again, thanks for your assistance, but I'm getting a syntax error with this.

Re: Remove expression based on filter selection

Thu, 03 Jan 2013 10:59:15 GMT

Can you describe your problem please ?

Where is the syntax error ?

Amand Dupretz

Re: Remove expression based on filter selection

Anonymous — Thu, 03 Jan 2013 11:03:21 GMT

Please see the attached image. I am developing in Qlikview 10, SR1 by the way.

Re: Remove expression based on filter selection

Gysbert_Wassenaar — Thu, 03 Jan 2013 11:19:37 GMT

If you're using QV10 then you can't use the alternate states syntax in your set expressions. You're also missing conditional display options for expressions. So probably the only course of action is to use two charts and show one chart if there are no selections in Class Of Business and hide the other. And reverse that when there are selections in Class Of Business.

Re: Remove expression based on filter selection

Anonymous — Thu, 03 Jan 2013 11:21:41 GMT

Thanks. I was already coming around to this way of thinking, but was there was an alternative solution.

Thanks to everyone for their assistance though.

Re: Remove expression based on filter selection

Anonymous — Thu, 03 Jan 2013 13:46:02 GMT

Sorry, I've gone and stumped myself again.

So I want to show a chart if NO selections have been made or it ONLY Class Of Business has been selected. Any other combination of field selections should result in hiding the "Market Average" chart.

Re: Remove expression based on filter selection

Thu, 03 Jan 2013 14:06:44 GMT

Okey, I understand.

I have a solution but I think it's improvable...

In your chart, go to "Layout" tab. Check "Conditionnal" in "Show" option. And the expression is :

=isnull(GetCurrentSelections())      /* NO SELECTION */
or                                                           /*     OR   */
(
     wildmatch(GetCurrentSelections(),'[Class Of Bussiness')

/* In the current selections, there is a value which concern [Class Of Business] */

and
substringcount(GetCurrentSelections('||||'),'||||')=0

/* There is only one field where there is a selection (bof...) */
)

Amand Dupretz

Re: Remove expression based on filter selection

Tue, 08 Jan 2013 03:25:11 GMT

I've just hit a similar problem myself and came across this thread while looking for a solution. While figuring it out I've found perhaps not the prettiest solution, but it works and I thought I would share anyway

So what I had is a chart (line graph) with two expressions, and five filters (in a multibox) against it. However, one filter is not relevant to one of the expressions and makes the comparison between the two expressions misleading if used, so I wanted to hide that expression if it's selected, and reveal it again if that filter is deselected.

I'll call the field in question that can be filtered fieldName for this example.

I first created two variables - countRecords and isFiltered, the latter I gave an initial value of 0

Under Document Event Triggers, I set an OnOpen trigger to Set Variable : countRecords = count(DISTINCT fieldName)

Under Field Event Triggers, I also set an OnSelect trigger against fieldName to Set Variable : isFiltered = if(count(DISTINCT fieldName) < countRecords, 1, 0)

Then finally I wrapped my chart's expression in the following : =if(isFiltered = 1, 0, (original expression))

Save & close, open it up again, and it works - I have a conditional expression in my chart which makes it disappear if a particular field is filtered. It's not elegant, but it's a quick fix that seems to work and that's all I need

Hope it helps point you in the right direction!