24 Replies Latest reply: Aug 17, 2016 3:29 PM by Chandni Bhowmick RSS

    How to filter a table by a measure

      Hey there,

       

      I have a table with various dimensions and one measure.

       

      Now, I always(pre-selected) want to display data were the measure has the value 1.

       

      But how can I filter it?

       

      Anyone can help me?

        • Re: How to filter a table by a measure
          Manish Kachhia

          Go to settings

          Document properties

          OnOpen Event

          Trigger Select to Select IN FIELD

           

          FIELD

          measure

           

          Search String

          =1

          • Re: How to filter a table by a measure
            Jonathan Poole

            Couple ways:

             

            1.  Change the dimension to be conditionally NULL if the measure<>1 , and then unselect 'show nulls'

             

            So if the table had one Dimension (City) and the measure was count(customer) , you could change the dimension to conditionally show city depending on whether the count of customers aggregated by city was = 1  (or <>1 etc..)

             

            =if( aggr(Count(Customer),City)=1, City)

             

            Capture.PNG

             

            2.  Or use set analysis in the measure to filter for a set where the measure value =1

             

            Same example, leave the dimension as is (City) , and put in a set analysis modifier to filter for cities where count(Customer)=1.  Exact syntax is very important. And think about the right aggregation context... you may need to use a unique row identifier in lieu of just 'city' if you have multiple dimensions

             

            Count (   {<City={"=count(Customer)=1"}>} Customer)

              • Re: How to filter a table by a measure

                Thanks for your input.

                 

                Actually I think it's a little bit more complicated, at least for me

                I have 11 dimensions and one measure in my table.

                 

                The measure is calculated this way:

                 

                Count({$<MasterYear = {"<$(=(MasterYear))"},
                MasterMonth = ,
                Relevant_Sales = {1},
                Contract_Status_Code -= {0},
                Contract_Status_Code -= {10},
                DateType = {'Contract_Start'}>}DISTINCT(Contract_Key))

                +

                Count({$<MasterYear = {"=$(=(MasterYear))"},
                MasterMonth = {"<=$(=MAX(MasterMonth))"},
                Relevant_Sales = {1},
                Contract_Status_Code -= {0},
                Contract_Status_Code -= {10},
                DateType = {'Contract_Start'}>}DISTINCT(Contract_Key))

                -

                Count({$<MasterYear = {"<$(=(MasterYear))"},
                Relevant_Sales = {1},
                MasterMonth = ,
                Contract_Status_Code -= {0},
                Contract_Status_Code -= {10},
                DateType = {'Contract_End_Real'}>}DISTINCT(Contract_Key))

                -

                Count({$<MasterYear = {"=$(=(MasterYear))"},
                Relevant_Sales = {1},
                MasterMonth = {"<=$(=MAX(MasterMonth))"},
                Contract_Status_Code -= {0},
                Contract_Status_Code -= {10},
                DateType = {'Contract_End_Real'}>}DISTINCT(Contract_Key))

                 

                 

                It calculates the portfolio for a specific year/month. The actual result of this calculation is about a  few hundreds or thousands.

                 

                Now I want to display all contracts(records) in this table which are in my portfolio.

                When I use my 11 dimensions and the portfolio measure as measure in the table, I get all contracts(records).

                The portfolio column has two values:

                0 for records that are not in the actual portfolio calculation.

                1 for records that are in the actual portfolio calculation.

                 

                Logically, I want only these records with portfolio = 1

                 

                Your hint is a first step but I dont get it....

                  • Re: How to filter a table by a measure
                    Jonathan Poole

                    Going with option #2 seems tricky for this kind of measure.

                     

                    Option #1 might be easier. might

                     

                    Do any of these 11 dimensions in the table create a unique identifier value for each  row ?  

                     

                    If yes, you can use option #1 , but you need to reference each of the 11 dimensions as a comma delimitted list for the 2nd argument of the aggr() function. Something like the following except that instead of Count(Customer) you copy/paste in your large measure expression for the 1st argument.

                     

                    =if( aggr(Count(Customer),Dimension1,Dimension2,Dimension3,.....,Dimension11)=1, UniqueIdentifierDimension)


                    If no, things get tricky depending on the data model.


                    it may be relatively easy or relatively hard to create a unique identifier dimension in the load editor using the autonumber function:


                    NewTable:

                    Load

                         *,

                         autonumber(Dimension1&Dimension2&Dimension3& ...  &Dimension11,'UniqueIdentifier') as UniqueIdentifier

                    from <datasource>;


                    This creates a unique integer value for each combination of the 11 dimensions creating a new unique identifier for each row in the table you are putting together.   Once this id done  add this new unique identifier into the table and modify the expression to follow option #1:


                    =if( aggr(Count(Customer),UniqueIdentifier) 


                    The exact load script would need to be worked out depending on your data model and may even require some reorganization of the data model unfortunately.


                    Sorry this isn't much easier and feel free to share your QVF if you like for additional help.

                      • Re: How to filter a table by a measure

                        Thanks again

                         

                        The Contract_Key dimension is definitly unique.

                         

                        My custom dimension:

                         

                        if( aggr(
                        (Count({$<CanonicalYear = {"<$(=(CanonicalYear))"},
                        CanonicalMonth = ,
                        DateType = {'Contract_Start'}>}DISTINCT(Contract_Key))
                        +
                        Count({$<CanonicalYear = {"=$(=(CanonicalYear))"},
                        CanonicalMonth = {"<=$(=MAX(CanonicalMonth))"},
                        DateType = {'Contract_Start'}>}DISTINCT(Contract_Key))
                        -
                        Count({$<CanonicalYear = {"<$(=(CanonicalYear))"},
                        CanonicalMonth = ,
                        DateType = {'Contract_End'}>}DISTINCT(Contract_Key))
                        -
                        Count({$<CanonicalYear = {"=$(=(CanonicalYear))"},
                        CanonicalMonth = {"<=$(=MAX(CanonicalMonth))"},
                        DateType = {'Contract_End'}>}DISTINCT(Contract_Key)))
                        ,Country, Contract_No)=1, Contract_Key)

                         

                         

                        The result oft that:

                         

                        screen.bmp

                        On the left you cann see the different Contract_No and the value of the measure with 0 or 1. On the right my first tests with your suggestion.

                        How you can see... it does not work very well

                         

                         

                         

                        Do not get confused, the data here has only sample data with fewer dimensions/columns.

                         

                        The table object on the left is a good example:

                        All but one record are in the actual portfolio. (portfolio = 1). Only the thrid from button has the value 0. This record should not be displayed in the table.

                         

                        Is there anything that I'm doing wrong?

                         

                        Thank you very much for your help!

                         

                         

                          • Re: How to filter a table by a measure
                            Jonathan Poole

                            Ok... 

                             

                            First lets try to just add the aggr() expression as a new Dimension in the table. Can you copy/paste the expression below ?  It aggregates your expression by Contract_key.

                             

                            See if the table returns rows..

                             

                             

                            aggr(

                            (

                            Count({$<CanonicalYear = {"<$(=(CanonicalYear))"}, CanonicalMonth = , DateType = {'Contract_Start'}>}DISTINCT(Contract_Key))

                             

                            +

                             

                            Count({$<CanonicalYear = {"=$(=(CanonicalYear))"}, CanonicalMonth = {"<=$(=MAX(CanonicalMonth))"},DateType = {'Contract_Start'}>}DISTINCT(Contract_Key))

                             

                            -

                             

                            Count({$<CanonicalYear = {"<$(=(CanonicalYear))"}, CanonicalMonth = , DateType = {'Contract_End'}>}DISTINCT(Contract_Key))

                             

                            -

                             

                            Count({$<CanonicalYear = {"=$(=(CanonicalYear))"}, CanonicalMonth = {"<=$(=MAX(CanonicalMonth))"}, DateType = {'Contract_End'}>}DISTINCT(Contract_Key))

                            )

                            ,

                            Contract_Key)

                              • Re: How to filter a table by a measure

                                screen.jpg

                                 

                                Thats my expression for the CustomDim:

                                aggr(

                                (Count({$<CanonicalYear = {"<$(=(CanonicalYear))"}

                                , CanonicalMonth =

                                , DateType = {'Contract_Start'}>}DISTINCT(Contract_Key))

                                +

                                Count({$<CanonicalYear = {"=$(=(CanonicalYear))"}

                                , CanonicalMonth = {"<=$(=MAX(CanonicalMonth))"}

                                ,DateType = {'Contract_Start'}>}DISTINCT(Contract_Key))

                                -

                                Count({$<CanonicalYear = {"<$(=(CanonicalYear))"}

                                , CanonicalMonth =

                                , DateType = {'Contract_End'}>}DISTINCT(Contract_Key))

                                -

                                Count({$<CanonicalYear = {"=$(=(CanonicalYear))"}

                                , CanonicalMonth = {"<=$(=MAX(CanonicalMonth))"}

                                , DateType = {'Contract_End'}>}DISTINCT(Contract_Key))

                                ),Contract_Key)

                                 

                                Here is the source file:

                                screen.jpg

                                 

                                In the first screenshot year = 2013 is selected. So we have 4 Contracts which are active in the whole year 2013.

                                  • Re: How to filter a table by a measure
                                    Jonathan Poole

                                    hmm... something is wrong. It gave you just a NULL for that calculated dimension.  You should have had some numbers.

                                     

                                    If you open up that calculated dimension in the f(x) expression editor,  is  Contract_Key (2nd argument of aggr())  colored in black ??

                                     

                                    for example 'countries_world.Name' is a field, but qlik sense has colored it in black, which means it doesn't actually recognize it as a field name

                                    Capture.PNG

                                     

                                    if i put square brackets around that same text, qlik sense colors it in red, meaning that it knows its a field name

                                     

                                    Capture2.PNG

                                     

                                    we need to make sure the expression is recognizing Contract_Key as a field . Try puting [] around it and retest.

                                      • Re: How to filter a table by a measure

                                        Sorry for the late response, I was a few days out of the office due holidays here in Germany.

                                         

                                        Contract_Key is colored in red.

                                         

                                        screen.jpg

                                        Don't be confused... I tested it with our real project. Therefore the fields could be named a little bit different.

                                          • Re: How to filter a table by a measure
                                            Azam Mullick

                                            You could try wrapping your expression that calculates Portfolio in an IF() statement:

                                             

                                            if(<your_expression> = 1, 1, 0)

                                             

                                            It should then just display the rows where Portfolio returns 1

                                             

                                            Thanks,

                                            Azam

                                              • Re: How to filter a table by a measure

                                                Hey,

                                                 

                                                When I use this as my measure (Portfolio), I get just the same like before. All columns and the measure as extra column with value 0 or 1....

                                                 

                                                if(
                                                (Count({$<MasterYear = {"<$(=(MasterYear))"},
                                                MasterMonth = ,
                                                Relevant_Sales = {1},
                                                Contract_Status_Code -= {0},
                                                Contract_Status_Code -= {10},
                                                DateType = {'Contract_Start'}>}DISTINCT(Contract_Key))

                                                +

                                                Count({<MasterYear = {$(=(MasterYear))},
                                                MasterMonth = {"<=$(=MAX(MasterMonth))"},
                                                Relevant_Sales = {1},
                                                Contract_Status_Code -= {0},
                                                Contract_Status_Code -= {10},
                                                DateType = {'Contract_Start'}>}DISTINCT(Contract_Key))

                                                -

                                                Count({$<MasterYear = {"<$(=(MasterYear))"},
                                                Relevant_Sales = {1},
                                                MasterMonth = ,
                                                Contract_Status_Code -= {0},
                                                Contract_Status_Code -= {10},
                                                DateType = {'Contract_End_Real'}>}DISTINCT(Contract_Key))

                                                -

                                                Count({<MasterYear = {$(=(MasterYear))},
                                                Relevant_Sales = {1},
                                                MasterMonth = {"<=$(=MAX(MasterMonth))"},
                                                Contract_Status_Code -= {0},
                                                Contract_Status_Code -= {10},
                                                DateType = {'Contract_End_Real'}>}DISTINCT(Contract_Key))
                                                ) = 1,1, 0)

                                                 

                                                 

                                                My complete procedure:

                                                1. I'm adding a new measure with the code shown above. Thats my expression for the calculation of the portfolio. The normal return value is any number with the total amount of contracts.

                                                2. I'm adding the new measure as measure in my table.

                                                3. All rows are displayed, even the value of the measure is 0.

                                                 

                                                I don't know. Am I doing something wrong?

                                                  • Re: How to filter a table by a measure
                                                    Azam Mullick

                                                    Hi,

                                                     

                                                    Thanks for challenging me on this. You know, it's something I stunbled on by "accident" a while ago whilst trying to solve a similar kind of problem and didn't really think about why it appears to work...in fact what really makes it work is having the "Supress Zero-Values" checked in Presentation. So in fact in this case, as you're already evaluating Profile to 0, or 1 the If() statement is not necessary. However, this is in QlikView and I'm not sure if the same feature is available in QlikSense...so sorry if I've wasted your time.

                                                     

                                                    Azam

                                                      • Re: How to filter a table by a measure

                                                        Don't worry!

                                                        Thanks for your input.

                                                         

                                                        I'm afraid that there is only a possibility to suppress null values, not zeros.

                                                         

                                                        Maybe that is one disadvantage of Sense. You don't have the same possibilities to adjust your objects.

                                                         

                                                        But thats okay anyway. Both programs are for different purposes.

                                                         

                                                        The challenge is to find a workaround

                                                  • Re: How to filter a table by a measure
                                                    Jonathan Poole

                                                    Thanks very much.  Would be able to share the app ?

                                      • Re: How to filter a table by a measure
                                        Steven Black

                                        Thank you!!!

                                      • Re: How to filter a table by a measure

                                        Finally, here is a sample app where you can test it yourself.

                                         

                                        On the second worksheet ("Portfolio table") you have to select one year on the top of the sheet. (I suggest 2014)

                                        After that, you can see all rows with "MeasureTest" as last column with values {1} and {0}.

                                        The result of the portfolio calculation ist in this case very simple:

                                        - The contract has a start date before or equal 2014.

                                        - The contract has an end date > 2014.

                                         

                                        Because of that, there a 7 contracts that have the value 0 because there aren't in the porftolio for 2014

                                         

                                        So, actually the table should display only rows with MeasureTest's value is 1!

                                          • Re: How to filter a table by a measure
                                            Jonathan Poole

                                            Thanks so much

                                             

                                            Here is the App with the approach in place:

                                             

                                            The new dimension is as follows:

                                             

                                            if ( 

                                                 aggr(

                                             

                                             

                                                 if(

                                                      (Count({$<CanonicalYear = {"<$(=(CanonicalYear))"}, CanonicalMonth = ,

                                                      DateType = {'Contract_Start'}>}DISTINCT(Contract_Key))

                                                      +

                                                      Count({$<CanonicalYear = {"=$(=(CanonicalYear))"}, CanonicalMonth = {"<=$(=MAX(CanonicalMonth))"},

                                                      DateType = {'Contract_Start'}>}DISTINCT(Contract_Key))

                                                      -

                                                      Count({$<CanonicalYear = {"<$(=(CanonicalYear))"}, CanonicalMonth = ,

                                                      DateType = {'Contract_End'}>}DISTINCT(Contract_Key))

                                                      -

                                                      Count({$<CanonicalYear = {"=$(=(CanonicalYear))"}, CanonicalMonth = {"<=$(=MAX(CanonicalMonth))"},

                                                      DateType = {'Contract_End'}>}DISTINCT(Contract_Key))) = 1, 1, 0)

                                             

                                                 ,Contract_Key)=1 ,

                                             

                                            Contract_Key)

                                             

                                            Capture.PNG

                                              • Re: How to filter a table by a measure

                                                I am the only one who has to say thank you.

                                                 

                                                I will test it tomorrow in the real app.

                                                After that I will mark your answer as correct!

                                                 

                                                Many many thanks Jonathan!

                                                  • Re: How to filter a table by a measure
                                                    Jonathan Poole

                                                    Haha. No prob. Let me know how it goes. The basic method is as follows when you do have a dimension in the table that is a unique identifier for the chart

                                                     

                                                    if (

                                                         aggr(

                                                         <Exact Measure Expression Here>   

                                                         ,<Unique Dimension for the table rows Here>)=  <Value Filter in Here> ,

                                                    <Unique Dimension for the table rows Here>  )

                                                     

                                                    ...and hide the NULLs

                                                     

                                                    if you don't have a unique dimension, pick one of the dimensions and change the expression. When doing this figure out if its 2,3  or all dimensions that are need to identify each row uniquely and then list them in a comma delimitted format for the 2nd argument of aggr()

                                                     

                                                    if (

                                                         aggr(

                                                         <Exact Measure Expression Here>  

                                                         ,<Dimension1 Here>, <Dimension2 Here>,<Dimension3 Here> et.... )=  <Value Filter in Here> ,

                                                    <Current Dimension for the table rows Here>  )