9 Replies Latest reply: Apr 13, 2012 7:17 AM by Joe Kirwan RSS

    Change values on excel charts using and excel variable

      I have an excel file with a chart. Some of the values of such chart are linked through quite complex excel formulas to a variable which is in the same excel file but our of the chart. I would like to make a "What if Analysis" in Qlikview obtaining different values for the data of the chart as I give different values to the mentioned variable.

       

      Is there a guide I can follow to do such thing?

       

      Thanks,

       

      Juan

        • Change values on excel charts using and excel variable
          Joe Kirwan

          Hi

           

          Is this what you need?

           

          1.     You will need to create a Variable in Qlikview

          2.     You will need to build the chart in Qlikview, replicating the Excel formulas in the Qlikview expressions, and referencing the Variable created as Step 1 (ie this variable will replace the Excel Cell Address in the Qlikview expression)

          3.     Create an Input Box, displaying the Variable created in Step1

           

          The input box will allow you to select values for the variable which will be used by the expression to calculate the chart values.

           

          Rgds

           

          Joe

            • Change values on excel charts using and excel variable

              Joe,

               

              Thanks for your reply. I understand and you are right: that's what I need. Problem is that my degree of sofistication coding Qlikview formulas is very low and I was hoping there was a way around that. I realize there isn't.

               

              My chart is somehow complex with formulas likes this: "=(SI.ERROR(BUSCARV(B4;'Tabla 1'!$B$16:$C$18;2;FALSO);0)+(1-'Tabla 1'!$C$19)*P4)*'Tabla 1'!$F$13". No clue about how to do that on qulikview.

               

              Would be usefull to have a full guide of paralel analysis excel-qulikview.

               

              Thanks again for your help.

               

              Juan

                • Change values on excel charts using and excel variable
                  Joe Kirwan

                  Hi Juan

                   

                  First thing - I am not an expert Qlikview user!

                   

                  As an accountant, my background was in Excel before I started using Qlikview.

                   

                  A good understanding of Excel helps, but you do need to change your thinking to really understand Qlikview.

                   

                  This is a challenging (but enjoyable) part of the process.

                   

                  How to replace the Lookup (Buscar) function was one of the things I had difficulty understanding.

                   

                  Here is what I would do.

                   

                  1. Table1: Load your table (the one with cell B4).

                  2. Table2: Load the Lookup table B16:B19 (make sure Column B has same name as Column B in Table1)

                   

                  There is now an association between Table1 and Table2.

                   

                  If you build a chart with Table1.ColB as a dimension, and introduce Table2.ColC into an expression, it will compute the value of Table2.ColC associated with the ColB value.

                   

                  You can also use Left Join:

                   

                  Left Join Table 2 to Table 1 - this will populate Table 1 with a new column containing the value in Column C of Table 2 associated with the value of colun B

                   

                   

                  The other elements of the formula are variables.

                   

                  If variable is a constant n and applies to each value of ColB, just add a line to your script:

                   

                       n as C19

                   

                       This will create a field C19 for each line of data, with value n (equivalent in Excel to column named C19 with value n in each row).

                   

                  C19 will then appear as a Dimension for inclusion in expressions.

                   

                  Otherwise create variable using Create Input Box to allow for user entry of different values for the variable.

                   

                  Hope this helps!

                   

                  Joe

              • Change values on excel charts using and excel variable
                Christophe Brault

                Hi,

                 

                Qlikview really does'nt work like an excel file. Don't try to replicate excel formulas, but try to get the same result with qlikview method.

                 

                To help you, can you give us some example of your data and the result you need ?

                  • Re: Change values on excel charts using and excel variable

                    OK. Here goes a simplified version of what I need. I have an amount, say, 30.000, that I have to split among a group of guys. These guys are either Managers or Senior Managers; the criteria to split are these:

                    - They have to be Senior Managers; if the are just Managers, the get no split

                    - Both Managers and Senior Mangers have shares on the Company

                    - Senior Managers split the 30K accoring to the shares they own as a percentage on the total amount of shares owned by the Senior Managers

                     

                    See the chart as an example:

                     

                    NameStatusShares on Co.% on Co.% over total Senior  Manager SharesAmount Split
                    Joe 1Manager233,17%0,00%0
                    Joe 2Senior Manager344,69%8,72%2.615
                    Joe 3Manager456,21%0,00%0
                    Joe 4Senior Manager567,72%14,36%4.308
                    Joe 5Manager679,24%0,00%0
                    Joe 6Senior Manager7810,76%20,00%6.000
                    Joe 7Manager8912,28%0,00%0
                    Joe 8Senior Manager10013,79%25,64%7.692
                    Joe 9Manager11115,31%0,00%0
                    Joe 10Senior Manager12216,83%31,28%9.385

                     

                    I would need help to calculate on qlikview the three columns on the left ussing different values for the amount to be split (30K in the example).

                     

                    Again, thanks for your time and help.

                     

                    Juan