18 Replies Latest reply: Apr 16, 2012 12:41 PM by Ashutosh Paliwal RSS

variance

Yojas Samarth

If  Variance between Actual cost and Standared cost is coming in negative then how to display it (I want to display it as positive)?

  • variance
    Erica Whalley

    Hi yojassamarth

     

    Try  using the fabs() function around your expression. The result is the size of the number - ie the number as a positive.

     

    Regards

     

    Erica

    • Re: variance
      Ashutosh Paliwal

      Hi Yojas,

      As Erica said fabs() function will always return absolute (unsigned) value of the expression passed as parameter.

      So, if you want absolute value of your variance than pass your expression to fabs function.

       

      So, if you write fabs(Absolute Cost - Standard Cost) then it will return only value without its sign (absolute value). Also the same value will be returned if you write it otherwise fabs(Standard Cost - Absolute Cost).

       

      Warm Regards,

      Ashutosh

      • variance
        Yojas Samarth

        Hi

        And if i want to highlight that negative valued field with some color then what to do?

         

        yojas

        • Re: variance
          Ashutosh Paliwal

          Hi,

          This can be done using text color for your expression.

          Normally, You can use your expression name or can also use column() function so for example if your expression is column(1)  then you can put if (column(1) < 0, red(), black())

          or same way if your expression name is variance then you can put if (variance<0,red(),black()) .

          Qlikview by default shows column names in blue color.

           

          The same effect can also be achieved through Visual cues tab and you can put your conditions (select your column and put < 0 and choose your color) there also.

           

          But here you will not be able to use Column() function or your expression name to refer to the expression because that will return absolute value (no negative value) which will always be greater than 0.

          Also, You can not use visual cues because they works on the column values directly and you will never have any negative values in your variance expression column, since you are using fabs().

           

          So you have to use text color in your expression (click on +on your expression) and put the same expression you are using in variance column without fabs() in the text color definition.

          So, if your variance expression is fabs([Actual Cost] - [Standard Cost]) then in text color definition put

          if(([Actual Cost] - [Standard Cost]) < 0, red(), black())


          1.jpg

           

          I have tried to explain it in a little detail and I am worried that it can be confusing (bad writing skills ).

          So, Please let me know if any thing remains confusing.

           

          Thanks and Regards,

          Ashutosh

        • variance
          Ashutosh Paliwal

          Also, red() and Black() I have here used just for explanation, you can use any other color function as well as rgb and argb functions.

           

          Thanks

          Ashutosh

          • variance
            Yojas Samarth

            Thanks for detailed explanation......   

             

            yojas

            • variance
              Yojas Samarth

              i used if(actual cost-standard cost<0,red(),green()) and i am getting output correctly... but my column name becomes green after aplying this expression.... but i want it to be in black.... what to do?

               

               

              yojas

              • variance
                Yojas Samarth

                And if i want to hihglight negative values on bar charts or line charts then what to do? writting on color text  is not working....

                 

                 

                 

                yojas

                • Re: variance
                  Ashutosh Paliwal

                  Hi,

                  Yes, this was a long standing bug that if you use text color (This does not happen when you use visual cues) in table charts then the color which comes for the first row also comes for the label but it has been rectified in version 11.

                  As, I am attaching the file here in which you can see it. (May be if you open in version 10, you see the color but in verison 11 there is black color for the label).

                   

                  Also, in attached file, I have used text color only but it is showing the different colors on bars. (check values on data points)

                  I hope that I am not missing anything, Please let me know if any issue.

                • Re: variance
                  Ashutosh Paliwal

                  Also, I would suggest to download Qlikview 11 as SR 1 has also been made available.

                  • variance
                    Yojas Samarth

                    hmm...  Thanks.....

                     

                     

                    yojas

                    • variance
                      Yojas Samarth

                      i applied text color option on two diferent expressions ... for one field i am getting exact o/p as i want means the title is in black color.....but for second expression i am getting green color for heading why it is so??? as i am appling same thing for both exp. .... please help me out for this issue......

                       

                       

                      yojas

                      • Re: variance
                        Ashutosh Paliwal

                        Hi,

                        What is the text color of the first row for both the expression?

                        or, if you can share your application!!

                         

                        Thanks,

                        Ashutosh

                        • variance
                          Yojas Samarth

                          i used below expression for both the tables....but i am getting different o/p for both... how could it possible???

                          if(Field_Name<0,red(),green())

                           

                          yojas

                        • Re: variance
                          Yojas Samarth

                          i used below expression for both the tables....but i am getting different o/p for both... how could it possible???

                          if(Field_Name<0,red(),green())

                           

                          yojas

                          • variance
                            Ashutosh Paliwal

                            Hi,

                            Are both of the tables straight tables ? or One of them is Pivot table?

                             

                            Reagards,

                            Ashutosh

                          • variance
                            Ashutosh Paliwal

                            Can you share the application, if is not too big in size?

                          • variance
                            Ashutosh Paliwal

                            And, I believe that I somehow got confused at that time when I said that label color behaves differently in version 11 compared to earlier verisons, because I just now checked and it was same behavior for v 10 and 11.

                            But yes, label color was being changed to first row color in Pivot tables but in Straight tables it was consistent, and I believe I got confused that way only.

                            So, please accept my apologies for that.

                            And, If you are encountering this behavior in straight table then please let me see, even if you can give some sample app that will also do fine , because I am not able to regenerate this behavior on my system.

                            If I can see it then It will help me also to learn it better.

                             

                            As you see in this image, Pivot table (2nd) labels are green while straight table (1st) labels are black.

                             

                            1.jpg

                             

                            Thanks and Best Regards,

                            Ashutosh