5 Replies Latest reply: Aug 1, 2011 5:01 PM by Pablo Gálvez RSS

    divide by 0

    Pablo Gálvez

      Hi,

      I have my pivot table with many expressions. Some of them is calcuated by 0 because for formula. When I have 2 expression calculated for separated don´t give error and the expression shows the 'minus.png' because obviously (number/0). But when did it the same thing with another calculation the result gives nothing, just a blanks. I left an example:

       

      Thanks!!

        • divide by 0
          Pablo Gálvez

          I´m Sorry I forgot the values:

           

          328/0.95 = 345,3

           

          0/0 = Blanks

           

          I did this:

           

          IF(count({<AT1}>}(if (InYear (Fecha, Today(), 0), AT1)))/Sum( {$<YEAR = {$(#=Max(YEAR))}>} ATX) = ' ', 'qmem://<bundled>/BuiltIn/minus.png',

          IF(count({<AT1}>}(if (InYear (Fecha, Today(), 0), AT1)))/Sum( {$<YEAR = {$(#=Max(YEAR))}>} ATX) > 0,

          (count({<AT1}>}(if (InYear (Fecha, Today(), 0), AT1)))/Sum( {$<YEAR = {$(#=Max(YEAR))}>} ATX))))

           

          Thanks.

           

            • Re: divide by 0
              Stefan Wühl

              Hi,

               

              well, I am not sure if i got your point:

               

              Sum( {$<YEAR = {$(#=Max(YEAR))}>} ATX)

               

              results in 0?

               

              and

               

              count({<AT1}>}(if (InYear (Fecha, Today(), 0), AT1)))/Sum( {$<YEAR = {$(#=Max(YEAR))}>} ATX)

               

              gives you a blank?

               

              Well, I am not sure that comparing the above expression to a blank (one or more?) will work. It is better to check the first expression on zero.

               

              I also didn't get your point with

              328/0.95 = 345,3

               

              except that there is a mixup of decimal point format (it this just a type?).

               

              Stefan

                • Re: divide by 0
                  Pablo Gálvez

                  Hi, Swuehl thanks for answering, I just to showed you one line. There are many lines in my table with many results distinct that 0. Now the reason bacause gave 0 is due there is no data into the database for that month specificaly. So I have an expression that gave maior that 0 but I have another expression that in sometimes gave 0.

                   

                  count({<AT1}>}(if (InYear (Fecha, Today(), 0), AT1)))/Sum( {$<YEAR = {$(#=Max(YEAR))}>} ATX)

                   

                  is because no data into the database

                  Sum( {$<YEAR = {$(#=Max(YEAR))}>} ATX) = 0.

                   

                  but I did this:

                   

                  =count({<AT1}>}(if (InYear (Fecha, Today(), -1), AT1)))/if(Sum( {$<YEAR = {$(#=Min(YEAR))}>} ATX) = 0, '0,01',

                   

                   

                  so all the formulas is divide by 0,01 and no with 0. I don´t know what more to do. now the indicators changed to green.

                   

                  Thanks Again.

                   

                   

                   

                   

                   

                    • divide by 0
                      Stefan Wühl

                      So, do you still have a problem or are you satisfied with this solution?

                       

                      Depending on your business problem, it might be good to check for zero sum first and then calculate the division (if not zero), or use a fixed number like 100% or special expression that is confirm with the requirements of your business logic (or just state like 'n.a.' non available).

                       

                      Have a nice evening,

                      Stefan