6 Replies Latest reply: Nov 1, 2011 9:59 AM by Kiran Rokkam RSS

    if .. then... variables .... please help!

      Hi everybody,

       

      take a look at:

       

      22222.gif

       

      This does the following:

       

      3333.gif

       

      Is there a way to do it easyer?

       

      example:

       

      var SB = sum("sb")

      var SKY = sum(Sky)

      if SB > SKY then SB else SKY

       

      ???

      I'm asking because I have to integrate many other items like SB or SKY in the formula and it would get very complex.

       

      I think I can't use a QLIKVIEW VARIABLE ("set" or "let") because this is a formula based on a diagram chart with different dimensions...!)

       

      Any idea?

       

      Regards,

      jup

        • if .. then... variables .... please help!

          Go to variable editor and create:

           

          vSB: sum(HEL_PLACEMENT_={'728 X 90'}) HEL_DATE_IMP)

          similarly vSky. In the chart expression would be: if($(vSB)>$(vSky),'SB','SKY')

          Since, there is no equals sign for variable expression it will be computed dynamically.

           

          PS: The field names are not correct in my code, please use appropiate ones.

           

          KIran.

            • Re: if .. then... variables .... please help!

              Hi Kiran,

               

              I added a new variable "vSB" to the variable editor. This is the formula:

               

              111.gif

              But I get the expected result...: I'm using a

              pivot chart based on a daily calculation but the variable's calculation is not based on the daily DIMENSION, it's global :-/
              green = expected value (daily)
              red = variable's calculation (vSB)
              222.gif
              Any idea?
              regards,
              jup
                • Re: if .. then... variables .... please help!

                  Remove "=" sign in the variable and in the chart make the expression as =$(vSB)

                   

                  Kiran.

                    • Re: if .. then... variables .... please help!

                      You did it! :-)

                      Great job!

                       

                      Many thanks!!!

                      • Re: if .. then... variables .... please help!

                        Hi Kiran,

                        maybe you could help me with the next thing

                        Now, take a look at my (your) formula:

                         

                        111.gif

                        It works great  

                         

                        I have to integrate some other items to check, not only vSB and vSKY.

                        I have to add vMREC, vPOP, vINTER, vSTICKY ... to the formula.

                        But if I will only add "MREC", my formula will grow and grow and get more complex:

                         

                        222.gif

                         

                        ALso my the application speed will slow down (to calculate the object)

                         

                        Do you have any idea to create a shorter (easyer and faster) formula...?

                         

                        thx again,

                        jup

                          • Re: if .. then... variables .... please help!

                            Hi Jup,

                             

                            Good that it worked..!

                             

                            You are right, more memory will be consumed. I have an alternative that can work in straight table (for Pivot Table you need QV 11 ).

                             

                            Create a straight table with expressions, one each for a variable. Label the expressions as ValSB, ValSKY etc... This means that you computed each of these expressions for the dimensions. Create additional expressions in the same table but using the expression lables instead of the variables as :

                            If(ValSB>ValSKY,ValSB,ValSKY) etc.. This would decrease the calculation time. Now you can go to presentation tab and hide these columns ValSB, ValSKY etc..

                             

                            To contrast the ways:

                            In the former the expression is computed for each of the dimensions everytime variable is called ie., In your current expression, value of SB is calculated twice if the condition is true. In the latter expression is only computed once for the first time and used later on.

                             

                            I know this wont work for Pivot table as you cannot hide the expressions (which is there in QV11) but this is the best Idea I have as of now. Let me know if you have a better alternative.

                             

                            Regards,

                            Kiran.