16 Replies Latest reply: Jul 18, 2017 7:32 AM by Ozzie Boeuf RSS

    How to skip zero values

      Hi everyone,

       

      I have created measure which calculates customers debts and I have a lot of customers with zero debts.

      I use function Sum(Debet-Credit). An example shown below.

      I receive values, and I dont want to see last 3 customers (because I have a lot of records with zero values)

      Customers    Debt

      Customer1    1000

      Customer2    500

      Customer3    450

      Customer4    300

      Customer5    0

      Customer6    0

      Customer7    0

       

      Does anybody knows how to do it in Qlik Sense Desktop, I mean is there any function which skips zero values?

       

      Thank in advance for your help.

       

      Best regards

      Vidas

        • Re: How to skip zero values
          Enrique Colomer

          in the dimension, put this formula:

          =if(Sum(Debet-Credit)<>0, Customer, Null())

          and clik option to no show null values

          • Re: How to skip zero values
            Anand Chouhan

            Another way also in the expression part write

             

            If( Sum(Debet-Credit) > 0 ,Sum(Debet-Credit))


            Or


            If( Sum( Total Debet-Credit) > 0 ,Sum(Debet-Credit))

             

            This is for neglect the 0 values.

             

            Regards

            Anand

            • Re: How to skip zero values
              jagan mohan rao appala

              Hi,

              Supress zero values option in Presentation.

               

              Regards,

              Jagan.

              • Re: How to skip zero values
                Michael Tarallo

                Hello Vidas -

                 

                Please note : unchecking this "Show null values" option - will suppress null values only - if these values are represented as 0, this option will not work, can you please verify if this works for you. It does not work for me using your sample data.

                 

                 

                What does work is this:

                 

                Is select the dimension customers - and select the Limitation option and choose > 0

                 

                 

                I attached a sample for you. Copy to C:\Users\<user profile>\Documents\Qlik\Sense\Apps and press F5 in the hub to see the app.

                 

                Let us know how you do

                 

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

                 

                Regards,

                 

                Mike T

                Qlik

                  • Re: How to skip zero values

                    Michael,

                     

                    I am having the same issue but in my case I need to display both negative and positive values. Your solution excludes the negatives.

                     

                    The bigger issue here is that when a Sum is done on a null value the result is displayed as zero, surporess null does not work.... Feature or Bug?

                      • Re: How to skip zero values
                        Jonathan Poole

                        You can handle that in a set analysis expression.

                         

                        In mike's example , the following could replace  sum( Debt-Credit)

                         

                        Sum( {<Customers={"=sum(Debt-Credit)>0 or sum(Debt-Credit)<0"}>} Debt-Credit)

                         

                        This will only bring back customers where the sum was > or < 0.

                          • Re: How to skip zero values
                            Jonathan Poole

                            Also - just realized i didn't answer the 'bigger issue' question ... Can you post a sample QVF ? 

                             

                            The reason for the ask  is that there is a lot of subtle differences between true Null values, missing values  (type 1 and type 2) , and empty values . 

                             

                            http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/30/null-the-invisible-nothing

                             

                            http://community.qlik.com/docs/DOC-3155

                             

                            A sum() on missing values will usually produce a 0 (see pf 10-11 of null or nothing whitepaper), but if there are true NULL values that have been calculated as such in Qlik or loaded from a database ,  then things can change.

                              • Re: How to skip zero values

                                Thanks for your suggestions Jonathan. I tried inputting this in a few ways with similar results. You can check the qvf and xls. The data source we are actually using is Oracle. The values are loading as nulls. Unless I am still missing something it looks like sum of a null value is showing as zero.

                                  • Re: How to skip zero values
                                    Jonathan Poole

                                    From what i can gather the following documentation snippet is at play here..   a sum() on a field that has true nulls will 'not normally be affected by NULLs' and 'dont use the record with the NULL in the calculation'.  That *i think* means you are going to get a 0.

                                     

                                    Capture2.PNG

                                    So if i load data that has an explicit NULL (see below for a manufactured NULL in the load script)

                                    Capture.PNG

                                     

                                    The sum() shows 0 for the null record, but as a dimension it shows null() and the nullcount() function picks it up.

                                     

                                    Capture2.PNG

                                    hic

                                      • Re: How to skip zero values

                                        So it seems like the only way to suppress these manufactured 0 values in a table is to use a set expression? This is poor work around because it makes using a master expression not possible. The expression will need to reference the dimensions in the object.


                                        To me the way null is being handled/displayed in an aggregation function is incorrect. The Sum of a null is not 0. Do you agree or am I missing something?


                                        thanks for the help

                                          • Re: How to skip zero values
                                            Jonathan Poole

                                            The other way is use a calculated dimension that checks for Nulls in the measure expression. If its is a null , then assign null() and turn off the checkbox 'show nulls' for that same dimension.

                                             

                                            So in the previous example the first row can be supressed by changing F1 to be the following expression:

                                             

                                            if( aggr(nullcount(Amount)>0,F1),null(),F1)


                                            and turning off the 'show nulls' checkbox.


                                            As for the right / wrongness in how sum() works on NULLs that is worth posing as a followup to this blog entry. ( i also tagged HIC in the previous post in case he can comment directly).


                                            NULL handling in QlikView


                                            Capture.PNG

                                             

                                            Capture2.PNG

                                              • Re: How to skip zero values

                                                Thanks again Jonathan,

                                                 

                                                I guess it is bugging me so much because QlikView suppresses the zeros created by default. Where in Sense there is no option to easily do this. The results of sum(null()) are the same in both products, IMO QlikView handles this more gracefully. Suppress zeros is an option in the bar chart. I guess it just didn't make the cut for the table objects.

                                                3-13-2015 10-50-52 AM.png

                                                The work-arounds do work but also introduce a whole bunch of complexity and challenges as well. In the case you show above, a dimension would need to be created for each instance of a measure. The combination of these things can total in the hundreds making things very confusing.

                                                  • Re: How to skip zero values
                                                    Jonathan Poole

                                                    I don't disagree with you.  the suppress zeros option on the presentation tab of a chart object in QV is there for all charts and summarized tabular displays are INCLUDED in that... straight tables / pivot tables.

                                                     

                                                    In Sense, charts DO have a supress zero option in the addons section  (as patrick mentioned below) but this does not exist in a table object, even if the table object is very similar to a straight table in QV, does aggregations etc... zero suppression should be in there.

                                                     

                                                    I don't think this is the first community post i've seen on this . i'll pass it around internally.

                                      • Re: How to skip zero values
                                        Patrik Lundblad

                                        Hi David,

                                         

                                        Have you checked in the property panel in the add-ons section. There is a show zero value option there in data handling.

                                         

                                        Regards,

                                        Patrik

                                      • Re: How to skip zero values
                                        Adam Brian

                                        Hi micheal tarallo,

                                         

                                        from your file qvf, can i hide customer 3? i want all customer but not customer 3?