9 Replies Latest reply: Sep 25, 2013 12:00 PM by whiteline _ RSS

    Filter table based on it's values

      Hi guys

       

      I have a table with the following columns:

      ID, Col1, Col2

       

      I have a lot of rows in it, I want to filter the table only where Col1=Col2.

      I have tried doing this by using Set Analysis, but I haven't succeeded.

      Also, I am trying to accomplish this by a button, which means that I am trying to use an Action, but which one should I use? Select In Field?

       

       

      Thanks for your help!

        • Re: Filter table based on it's values
          whiteline _

          Hi.

           

          You can't use set analysis in such a case, because it is calculated once for the entire chart.

          You could do it using calculated dimension:

          =aggr(if("Col1=Col2", ID), ID)

          And toggle checkbox 'supress whten value is null'.

           

          Of course you should replace "Col1=Col2" with appropriate test condition.

          • Re: Filter table based on it's values
            Kabilan Kumarasamy

            try this

             

            =if(col1=col2,count(id))

             

            Regards,

            Kabilan K.

            • Re: Filter table based on it's values
              Rohit Koul

              well its Simple you can do it in Script like

               

              if(Col1 = Col2, 'Same','Different') as Flag,

               

              then you will have a Flag with two Options Select 'Same it will filter your data '

                • Re: Filter table based on it's values
                  Oleg Troyansky

                  Sometimes I wish answers were moderated for correctness, not just for spam...

                   

                  - For large tables with a lot of data, I would never recommend to use sum(if(...)) constructions for their poor performance.

                  - The other construction if(col1=col2, count(id)) simply won't return any meaningful results because col1 and col2 can't be uniquely identified outside of the aggregation function.

                   

                  Rohit's answer is the closest - you should calculate the Flag in the script. Now you can either use selection in the Flag field to force Same values or different values, or you can replace your whole expression with a different expression using an action that can be triggered by the button:

                   

                  sum( Value)

                       vs.

                  sum ({<Flag = {'Same'}>}   Value)

                   

                  Read the following blog article for an example of using Variables to make your formulas more flexible:

                   

                  Q-Tip #6 - Those Tricky $-Sign Expansions | Natural Synergies

                   

                  cheers,

                   

                  Oleg Troyansky

                  www.masterssummit.com

                    • Re: Filter table based on it's values
                      whiteline _

                      The person who asks moderates the answers for correctness.

                       

                      We can only guess what statements correspond to the names col1 and col2.

                      Suppose that there are some complex conditions with some aggregations, that dynamically depend on user selection and your solution with flag in script become not so elegant and easy.

                       

                      Of course separate flag is worth to achieve.

                       

                      But one size doesn't fit all. Sometimes even sum (Value*Flag) is preferable than sum ({<Flag = {'1'}>} Value)

                        • Re: Filter table based on it's values
                          Oleg Troyansky

                          Sometimes even sum (Value*Flag) is preferable than sum ({<Flag = {'1'}>} Value)

                          whiteline - I would be very curious to see an example of that. I have a lot of evidence, both theoretical and practical, suggesting the opposite. I've yet to see an example where sum(Value*Flag) could be preferable to sum({<Flag={1}>} Value)

                           

                          Please, explain what you meant.

                           

                          cheers,

                           

                          Oleg Troyansky

                            • Re: Filter table based on it's values
                              whiteline _

                              Oleg, I know that sets should have better performance. But sometimes it's not about performance.

                               

                              The first that come to mind is a complex task where nested $-expansion with parameters is used. The expression stored in a field is used to change calculations dynamicaly depending on what expression the user selects. $-expansion fails with set syntax.

                               

                              Second, I've done some 50M tests and the multiplication is not so bad, especially when you don't want to explain the users what means that strange bracets.

                               

                              Third, there was thread here where multiplication was better in reality.