Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

qplaiukl
New Contributor III

Filter chart

Hi,

I need to filter data in a chart based on its category. In particular, category 660 needs to be filtered. The second requirement is that if 660 has been filtered for a certain customer, also all data with category 74 needs to be filtered for this customer.

So, basically, I need not exclude all rows for customer A in this example. But I cannot simply filter customer A, because if customer A has another category than 660 or 74, he needs to stay.

CustomerCategoryAmount 1Amount 2
A660500
B61000
A740100
C740150

For column "Amount 1"  I use the following expression within the chart:

sum(if(Category<>660,Amount1))

But what expression do I need for column "Amount 2" in order to filter the third row?

Many thanks,

Paul



1 Solution

Accepted Solutions
ravic906
Contributor III

Re: Filter chart

Hey!

See the attachment. Is this what you are trying to achieve?

9 Replies
MVP & Luminary
MVP & Luminary

Re: Filter chart

I don't really understand what do you want to filter but I think it should be work with set analysis like:

sum({< Category = {660, 74}, Customer = {'A'}>} Amount1)

With set analysis are various multiple filter possible - here is a good overview for this: Set Analysis: syntaxes, examples

- Marcus

qplaiukl
New Contributor III

Re: Filter chart

Hi Marcus,

thanks for your help. Let me explain this more detailed.

This is the point of start:

Customer CategoryAmount 1Amount 2
A660500
B61000
A740100
C740150

Then I apply this expression for Amount 1: sum(if(Category<>660,Amount 1)). The result is the following:

Customer CategoryAmount 1Amount 2
B61000
A740100
C740

150

Now I need to filter Amount 2 in order to exclude the second row for customer A. For Amount 2, I can use the following expression: sum(if (Customer <> 'A' or Category <>74, Amount 2). This is the result:

Customer CategoryAmount 1Amount 2
B61000
C740150

However, I want the filter to be dynamic, which means not to limit to Customer A within the second expression. It should be able to apply the logic to new customers without changing the expression.

Many thanks,

Paul

MVP & Luminary
MVP & Luminary

Re: Filter chart

Those filter-criteria must not be static - you could replace them with expressions like getfieldselections() or concat() and many more - especially by using set analysis which could extended to consider from selections excluded values. It's a very powerfully tool - not quite easy at the beginning from the syntax - but definitely worth to make the efforts to explore it and you will need it in many situations. The mentioned link above will be here very useful as a starting point.

- Marcus

qplaiukl
New Contributor III

Re: Filter chart

Thank you!

ravic906
Contributor III

Re: Filter chart

Hey!

See the attachment. Is this what you are trying to achieve?

qplaiukl
New Contributor III

Re: Filter chart

That's exactly it! Nice solution! Thanks!

rsatish111
New Contributor III

Re: Filter chart

Paul, please provide more details like :

1) Is the category value always will be 660? If it can change then provide detail logic to exclude category?

2) If category is fixed 660 then write a logic like " exclude those customer which has category as 660.

3) Above logic will help you to exclude customer A. Two records from above tables.

Thanks

qplaiukl
New Contributor III

Re: Filter chart

Hi A B,

1) No, the value for A is not always 660 or 74. It can be all values.

3) I have noticed it and adapted the solution of

load *, if(WildMatch(concat,'*660*,*74*'),'remove','keep') as flag;
load Customer,concat(distinct Category,',') as concat
Resident Load
Group by Customer

     (if(flag = 'keep' or Category <> 74, [Amount 2]))

qplaiukl
New Contributor III

Re: Filter chart

For the sake of completeness: In the meantime I have found another solution using nested set analysis:

Expression for Amount 2:

=sum([Amount 2])
-
sum({<
Customer ={"=count(distinct{<
                      Category = {660}
                     >}Customer)"
},
Category = {74}
>}
[Amount 2])