11 Replies Latest reply: Jun 12, 2015 5:12 AM by Davide Di Stefano RSS

    How to ignore selection in a table

      Hi all.

       

      I have the following problem: When I select a year, and some data doesn't exist for this year, how can I show the value 0 instead of the row not be shown? When nothing is selected, and the table is shown as table below:

       

      VariableValues - Sum(Valor)
      A100
      B200
      C0
      D30
      F0

       

      But if I select something, like year = 2015, since there's no data for row C and F, only the rows A, B and D are shown.

      VariableValues - Sum(Valor)
      A30
      B50
      D10
        • Re: How to ignore selection in a table
          Prem Kumar Thangallapally

          go to chart properties >> presentation >> uncheck supress zero values

          Untitled.png

          • Re: How to ignore selection in a table
            Andrew Whitfield

            Hi Janilson,

             

            'Suppress Zero Value' is set to TRUE by default, you just need to un-tick the option on the presentation tab of your charts properties.

             

            Andy

              • Re: How to ignore selection in a table

                Thanks for quick answer.

                I am using a Table Object in Qlik Sense, I've searched in it and in fact, I did found something like it. (Monstrar valores nulos = Show null values) as figure below.

                But when I select something, my table get smaller, and only field sum(value) that exists are shown.

                  • Re: How to ignore selection in a table
                    Michael Tarallo

                    Hi Janilson - please take a look at this two part video series on Set Analysis - this may help you do what you are looking to do:

                     

                     

                    from:

                     

                    New to Qlik Sense Videos

                     

                    Set Analysis allows you to create metrics using Sets of data that you define, including the ability to ignore current selections.

                     

                    Let us know how you do.

                     

                    Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

                     

                    Regards,

                    Mike Tarallo

                    Qlik

                      • Re: How to ignore selection in a table

                        I've already saw those examples (congratulations for them by the way), thanks Michael.

                         

                        I don't know how to use set analysis in measurements and that's what I need.

                        I want the sum to be simple, if there are values then sum it, if there isn't, then put zero in sum field.

                        Is it possible to use set analysis in a table dimension so all the rows of the table is shown, even if there's no data to represent/sumarize?

                         

                        OBS: the metric shall respect the selection, but not the table. But metric will need to show zero in data that it doesn't have.

                          • Re: How to ignore selection in a table
                            Michael Tarallo

                            Hi Janilson,

                             

                            Set Analysis is always / only used in Measures and not in dimensions - as it works with an aggregate function such as Sum() or Count() etc.

                             

                            Let's see if this gets us closer to what you want to do.

                             

                            You can combine IF THEN conditional logic in the expression. See this simple example to get an idea:

                             

                            IF(Sum({1<Country={'Germany'}>}Sales) < 0, 0, Sum({1<Country={'Germany'}>}Sales))
                            

                             

                            This states:

                             

                            If the  Sum of Sales for the country of Germany (ignoring other selections) is less than 0 - then just use 0, else give me the actual sum of sales for Germany.

                             

                            Note 1 indicates - to ignore current selections. - You would use this a measure in your table.

                             

                             

                             

                            Is this closer to what you are looking to do?

                             

                            IF not - did you see the option in the Add on section:

                             

                             

                             

                            Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

                             

                            Regards,

                            Mike Tarallo

                            Qlik

                              • Re: How to ignore selection in a table

                                Hi Michael.

                                First of all, thanks again for reply.

                                 

                                I don't want to change calculations or measures. So maybe set analysis is not for me.

                                Let me show you my application:

                                 

                                This is when I select nothing, see that all values of table (RB, A, B, RL) are show with some Totals 0, these totals means that there's no data to these rows

                                 

                                If I select some year, only rows that has some data is shown, of course it is the normal thing to happens, all those columns belongs to only one table except the sum(value) that is Total.

                                I need the table remains with all rows (even when some filter is selected), if the there's no data then it must show the zero. I do need the measure to change when I use filter, the measure needs to behave according to filter, but I don't want the dimension/table to change, I need every row to be shown, some with zeros, some with values.

                                 

                                OBS: I did see the option to show zeros, this is enabled, but I don't know if this would be enough.

                                 

                                Michael Tarallo &lt;span class=&quot;icon-status-icon icon-employee&quot; title=&quot;Employee&quot;&gt;&lt;/span&gt; escreveu:

                                 

                                 

                                If the  Sum of Sales for the country of Germany (ignoring other selections) is less than 0 - then just use 0, else give me the actual sum of sales for Germany.

                                 

                                Note 1 indicates - to ignore current selections. - You would use this a measure in your table.

                                 

                                 

                                Is this closer to what you are looking to do?

                                 

                                IF not - did you see the option in the Add on section:

                                 

                                 

                                 

                      • Re: How to ignore selection in a table
                        Davide Di Stefano

                        Hi,

                         

                        I had a similar problem with the necessity to show the name of a a dimension in a simple table also if the expression's values are 0, that I resolved with a trick.

                         

                        In the table I've defined an expression that is always true, than in the presentation tab I've hide it.

                         

                        Now Work!!! resolved_static dimension.tif