Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
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?

View solution in original post

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?

View solution in original post

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]))

Highlighted
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])