3 Replies Latest reply: Feb 11, 2014 8:51 AM by Jeremiah Kurpat RSS

    Alternative display of Values with a condition

      Hi,

       

      I want to calculate the average of Schnitt_SMS and Schnitt_WEB. Schnitt_SMS gives an mathematical error,

      so that Schnitt WEB und SMS can not be calculated. Does anyone have a solution to solve this problem with the "Condition"-

      Field.

      ....is not one of the two predictable....show only the predictable...

       

      Thanks...QlikView x64 - [Hp14890_dataTest_Formel_BedingungenTest.gif

        • Re: Alternative display of Values with a condition
          Jeremiah Kurpat

          Hi Ingo,

          Try using the rangesum function, that way if there is a null, you will still get the other numbers since sum will treat it as 0. I also cleaned up your expressions to use set analysis. Used this expression:

           

          rangesum(sum({<Kanal = {'WEB'}, Antwort = {'>=1'}>}Antwort)

          /

          count({<Kanal = {'WEB'}, Antwort = {'>=1'}>}Antwort)

          ,

          sum({<Kanal = {'SMS'}, Antwort = {'>=1'}>}Antwort)

          /

          count({<Kanal = {'SMS'}, Antwort = {'>=1'}>}Antwort))

          /

          2

           

          Please find attached.

           

          Hope this helps!

          • Re: Alternative display of Values with a condition

            Hey...

             

            thanks...

             

            If I use this expression I get this:

             

            2.gif

             

            the result should be 4.66 (Field Schnitt WEB und SMS). So, I need a condition that says...if one of the Values (in this example "Schnitt SMS" is not predictable, display only the other one (In this Example "Schnitt WEB") in the Field "Schnitt WEB und SMS"...

              • Re: Re: Alternative display of Values with a condition
                Jeremiah Kurpat

                Im guessing if they are both predictable, then add them together and divide by 2 like you had in your expression?

                 

                Try using the alt function like:

                 

                alt((sum({<Kanal = {'WEB'}, Antwort = {'>=1'}>}Antwort)/count({<Kanal = {'WEB'}, Antwort = {'>=1'}>}Antwort)+

                sum({<Kanal = {'SMS'}, Antwort = {'>=1'}>}Antwort)/count({<Kanal = {'SMS'}, Antwort = {'>=1'}>}Antwort))/2

                ,

                sum({<Kanal = {'WEB'}, Antwort = {'>=1'}>}Antwort)

                /

                count({<Kanal = {'WEB'}, Antwort = {'>=1'}>}Antwort)

                ,

                sum({<Kanal = {'SMS'}, Antwort = {'>=1'}>}Antwort)

                /

                count({<Kanal = {'SMS'}, Antwort = {'>=1'}>}Antwort))

                 

                PFA

                 

                Hope this helps!