9 Replies Latest reply: Sep 20, 2016 10:05 AM by Paul Pichler RSS

    Filter chart

    Paul Pichler

      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



       

        • Re: Filter chart
          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

          • Re: Filter chart
            Paul Pichler

            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

              • Re: Filter chart
                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

              • Re: Filter chart
                Ravichandra Nadiminti

                Hey!

                 

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

                • Re: Filter chart
                  A B

                  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

                    • Re: Filter chart
                      Paul Pichler

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

                        • Re: Filter chart
                          Paul Pichler

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