Filter chart

[Anonymous]

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.

Customer	Category	Amount 1	Amount 2
A	660	50	0
B	6	100	0
A	74	0	100
C	74	0	150

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

[marcus_sommer]

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

[Anonymous]

Hi Marcus,

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

This is the point of start:

Customer	Category	Amount 1	Amount 2
A	660	50	0
B	6	100	0
A	74	0	100
C	74	0	150

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

Customer	Category	Amount 1	Amount 2
B	6	100	0
A	74	0	100
C	74	0	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	Category	Amount 1	Amount 2
B	6	100	0
C	74	0	150

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

[marcus_sommer]

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

[Anonymous]

Thank you!

[Anonymous · Accepted solution]

Hey!

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

[Anonymous]

That's exactly it! Nice solution! Thanks!

[Anonymous]

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

[Anonymous]

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 Ravichandra Nadiminti as follows:

a) Load:

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

b) Chart:

Expression for Amount 1:

     (if(Category <>660, [Amount 1]))

Expression for Amount 2:

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

[Anonymous]

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