17 Replies Latest reply: Dec 21, 2009 7:31 PM by cjm RSS

    !Still not Solved. plz look, WhatIF scenario using inputfield in a pivot with Percentage

    DANIEL CHOTZEN

      hi all

      i have a transaction table (EG Sales)

      and hirachycal dimension is conected to the transactions table (EG customer super groups / customer group / customer)

      i am presenting this in a pivot table and it works great. sum(sales)

      now i want to add an inputfield in the script and use it in the pivot in a way that will allow the user to enter a Percentage number (between -100 and +100) into the inputfield

      and then use this number to calculate a simulated column in the pivot that will show the change acording to the Percentage entered.

      now in a pivot you cannot use inputfield (updatable ones).

      you can only use inputAvg and inputSum.

      the problem is that when it distibutes the value enterd in to the underlaying rows. it does so using the values in the inputfield but what i need is a distribution based on the value in the "sum(sales)" field.

      bcouse otherwise the calculation is wrong.

      hope im making myself clear here

      any ideas???

      Mansyno (aka oded@b-eye-turkey.com)

        • WhatIF scenario using inputfield in a pivot with Percentage

          Hello Mansyno,

          see the attached exmple.

           

          Rainer

            • WhatIF scenario using inputfield in a pivot with Percentage
              DANIEL CHOTZEN

              Hello there Rainer,

              thanks for your fast reply.

              i guess it is my fault. i might have been a bit unclear.

              first of all i need to use "inputField" (the special keyword that when preceeds a load statement makes the filed available for input in the layout)

              not a variable with an "inputBox". this i know how to use and used it in many documents.

              i have attched an example document.

              if you try to change the inputField of one of the subtotals then QV is suppos to distribute this value to the underlaying rows. and also to the cells in the pivot.

              the problem is that it distributes the sumed value acording to the number of rows conatined in each sub dimesnion, hence its useless in this scenario

              thanks

              Mansyno

                • WhatIF scenario using inputfield in a pivot with Percentage
                  Oleg Troyansky

                  Mansyno,

                  I'm using input fields in a similar situation, and I can't understand why would you want to distribute percentage based on Sales... If you are trying to project next year Sales using percentage increase, I'd expect you to use inputavg() and distribute the values identically - 10% across all underlying values. Then, you'd apply the 10% increase to the prior year sales.

                  Alternatively, you can define an inpute field like "Forecasted Sales", load Last Year Sales as initial values and let Users update the field. THis way, you'd enter absolute values, not percentages. In this case, QlikView can distribute the entered total based on the initial values.

                  Does it make sense ?

                  Oleg

                    • WhatIF scenario using inputfield in a pivot with Percentage
                      DANIEL CHOTZEN

                      Oleg,

                      ill start from the buttom, yes ofcourse what you are saying make sense.

                      the problem is not the distribution to the underlaying individual rows.

                      the probelm is with the underlaying dimesnions.

                      lets say i have a customer_group-->Customer hierarchy

                      and there is customer_group A with 4 Customer 1-2-3-4

                      but each customer have adirent number of sale rows, like this:

                      cust

                      1, 3 lines

                      2, 2 lines

                      3, 7 lines

                      4, 12 lines

                      if you give the user the inputAvg input field (lets name it "Inc")

                      and he enters 10 in the subtotal for the customer_group A then it makes 10 for each of the 4 Customers

                      then it will be sum(sales) * 1.10 for all 4 cells in the pivot // the formual looks like this sum(sales) * (1 + ([Inc]/100))

                      and this is correct

                      but if instead the user elters one of the Customer cells, lets say cust 2 and input a 10 value there.

                      now becouse customer 2 has 2 lines of transactions, each line will get a value of 5 (10/2lines)

                      but now the subtotal for the inputfield (inc) will be 0.08

                      but this is incorebt. the increase of 10% in the cust 1 sales does not make increase of 8% for the entire customer_group A

                      and if you look closely at these numbers tho you will see that the 0.8 comes from deviding 2 (lines) with the total number of lines

                      2/24=0.083

                      * in this example im asuming the use of the default distribution mode of the inputAvg function like this: inputAvg(Inc)

                       

                       

                      now :)

                      Does it make sense ?

                      Mansyno

                      oh and btw, the same problems apears if you use your 2nd alternative . the one that uses ablsolute values

                      im sure you can work out the details:)

                       

                      to anyone else reading this

                      This is not solved yet

                      is there abug here?

                      a conceptual bug mabybe?

                      please help:)

                      Mansyno

                        • WhatIF scenario using inputfield in a pivot with Percentage
                          Oleg Troyansky

                          Mansyno,

                           

                          I think, the "conceptual bug" is using "default" Distribution (which will divide the amount equally between all the lines) when your business scenario is asking for "equal" distribution (when the same value - 10% - is given to all underlying lines).

                          Try this: inputavg(Inc, '=') and see if it solves your problem.

                          If you still have a problem, please post an example.

                          thanks!

                          Oleg

                            • WhatIF scenario using inputfield in a pivot with Percentage
                              DANIEL CHOTZEN

                              Oleg,

                              well as a matter of fact i did try it also using this method: " inputavg(Inc, '=') "

                              and the results are worse.

                              becouse it disregards that you told it to use an AVG and it doubls the amount for the cust 1

                              but anyhow it is still uses this formula - number of line per customer / number of total lines for Customer_Group A

                              when it calculates the subtotal for the Customer_Group A

                              i have attached a working Document in one of the previous posts in this thread

                              it can demonstrate the problem

                              i have tried this model with both with the InputAvg() function and the InputSum() Function

                              and used all the avaliable distribuiton modes and even the qulifiers

                              but the problem still persists

                              in some cases it can be reveresd btw

                              meaning that when you put the value in the inside dimensions (EG customers) it calculates corectly for the subtotal of the Cusotmer_Group

                              but if you change the subtotal for the Customer_Group, it distributes in incorectly to the customers.

                              Mansyno

                              • WhatIF scenario using inputfield in a pivot with Percentage
                                DANIEL CHOTZEN

                                Hi Oleg and other fellow qlikers,

                                since its been sometime and no one have found a solution i am reposting the example file with some more explenation of the situation.

                                to sumerise:

                                if you have a pivot with an hierarchy of more the one dimensions

                                and you want to have a filed where the user types a precentage (1-100%) and the cell value of another expression reflects the change to the orignal number

                                (an example application of this is when you want to show WHATIF changes to your "Profit&loss" for current year when you raise or lower certain expnses or incomes)

                                the problem is in the caclucation of the contribution of the change of value in a cell level to the total of the level above it in the hierarchy, which is done wrong to my humble opinion.

                                attached is a sample document

                                hope to find an answer

                                Mansyno

                                  • WhatIF scenario using inputfield in a pivot with Percentage
                                    Oleg Troyansky

                                    Mansyno,

                                    I played with your sample, and I must admit - using "=" distribution doesn't work in your case. However, default distribution works just the way it's described.

                                    For example, I added 10% to your Sales number (??????) , and that caused a 9.12% increase at the level above, because Sales represent approx. 91% of the total volume - not in $ representation, but perhaps in the number of lines? How else could you possibly distribute the change ?

                                    If you need your values to be proportionate to the existing $ Amounts, the only way I know is to load the amounts into an input field and let the User enter the new amount (as opposed to the % change).

                                    Or, alternatively, you could always "force" the user to start Top-Down (first determine the % Change for the whole Company, then move down to the next level of detail, etc...) and use "T" modifier to enforse the total levels.

                                    Oleg

                                    P.S. How could you possibly know that all of us in QlikCommunity are fluent in Hebrew ? Wink

                                      • WhatIF scenario using inputfield in a pivot with Percentage
                                        DANIEL CHOTZEN

                                        hi oleg

                                        thanks for the reply

                                        you say:

                                        "......., but perhaps in the number of lines? How else could you possibly distribute the change ?"

                                        its not the problem of distributing the values as much as it is the aggration of it

                                        which apears not acording to what we expect (which is the real contribuiton of eash increase on each line)

                                        knowing how many lines contributed in each low level is irelevent fact.

                                        This WHATIF scenario is a very common request from customers

                                        but i have yet to find a way of implemnting it in QV

                                        the alternative you sugested are just not it im a fraid.

                                        the same problem is there when you use absolute values in stead of the % (check it out )

                                        and as for the top down aproach..... well that is maybe another way of represingng data but it is not the one our customers seek.

                                        and i can hear my coworkers starting to wisper in my ears: "excel, excel,,,,," :)

                                        oh and btw ???? ??? i thought i eliminatad all ocurences of hebrew in the document. guess i missed some

                                        Mansyno

                                         

                                         

                                          • WhatIF scenario using inputfield in a pivot with Percentage
                                            DANIEL CHOTZEN

                                            Hi, i apologize for doing this but its been few days and the post drifted down to the 5th or 6th page un answered

                                            please if in any of the xperts can read this thread and see if they contribute

                                            thanks

                                            Mansyno

                                              • WhatIF scenario using inputfield in a pivot with Percentage
                                                jvg

                                                Hi Mansyno,

                                                I've had a look at your problem, find attached a working solution. A few learning points mentioned below:

                                                In order to overcome your analysis requirements, I would look to datamodelling for the answer. If you wish to associate a factor with data of the granularity: 'NAME_51, NAME_52, NAME_53, NAME_54 and ACC_NAME' then create a table that contains data at this level, adding on your input field at this level.

                                                In the front-end you can now either present the end user with a table box of your fields, or use your inputavg in proportionate ( * ) mode to assign factors to one or more levels.

                                                Happy Qliking!

                                                Jonas

                                                  • WhatIF scenario using inputfield in a pivot with Percentage
                                                    DANIEL CHOTZEN

                                                    hi there Jonas,

                                                    thank you for the reply.

                                                    im not really sure what you changed in the script part of your answer but the more imprtent thing is that your QVW is not solving the problem still.

                                                    i attached an annotated version of your QVW.

                                                    if you check the numbers you see the the accumalation of the values in the higher levels of the pivot(hierarchy) is wrongly calculated compared to the values in the lower lvls, after the change in the inputfield.

                                                    again, when changing the input value in the upper level it distributes the changes correctly to its lower lvls but.....

                                                    it is accumelating incorrectly towrds its upper levels.

                                                    guess osmthining is sliping from my eyes, if i am the only one who can see it

                                                    Thanks for everything anyways

                                                    Mansyno

                                                     

                                                      • WhatIF scenario using inputfield in a pivot with Percentage
                                                        jvg

                                                        Hi Mansyno,

                                                        There were two problems that needed sorting:

                                                        1. My initial script with the 'DISTINCT' keyword, didn't actually make the data distinct for each key - this I spotted by looking in the Table Viewer, hovering over the %Key field. If it does not say 'Primary Key' the key is not unique. A two-step approach in the script has now taken care of that. I would recommend studying in detail how the extra table has been created.

                                                        2. Your total including % change had a slight logic error in it. When applying factors, these are easiest to apply at row-level. I.e. one cannot (easily) first aggregate a sum and then apply a cumulative % change. Find in the new revised document how a sum at row-level is achieved, and your £ 0.50 added is cumulated as required.

                                                        I hope that made more sense and satisfies your requirements.

                                                        Jonas

                                                          • WhatIF scenario using inputfield in a pivot with Percentage
                                                            DANIEL CHOTZEN

                                                            Hi there again Jonas,

                                                            i have studied your script more carefully this time and i understand the concept.

                                                            i use link table and uniqe %key table too and i agree that it have its advantge, you have used this method wisley to solve one side of the problem and indeed the sum of rows total for the new expression you used [Total inc. % Change] indeed is corect and was increased by my 50 Cents.

                                                            but i will pay you (and anyone else who sloves it) another 50 Cents if you can fix the problem with the field [% Change] whice now at the total level shows 2.50%

                                                            no way 50 Cents are 2.50% out of £ 9,707.43

                                                            i think i mentioed in one of the prevous posts in this thread how this problem is strange in that you cannot solve both sides of the problem simultainsouly

                                                            for example.

                                                            it works ok when you look from top to buttom, when you put 10% inc in top lvl it gets distributed corectly to lower lvls

                                                            but if you go buttom to top, and look to the upper lvl its is wrong.

                                                             

                                                            it also seems what you found is another case like that

                                                            where you can corectly show the right inc in Absolute amount, you cannot at the same time show corect accumalation for the % itself

                                                            i hope i make myself clear

                                                            Thanks for your Time and pataince(oh how i wis hthere was a spell checker in the new forum system)

                                                            Mansyno

                                                             

                                                            its also to bad that there isnt an option to mark an post as partialy answering the question thread :) lol

                                    • !Still not Solved. plz look, WhatIF scenario using inputfield in a pivot with Percentage

                                      Hi,

                                      Not too sure this is what you are looking for, especially considering the fact that the post ios more than 8 months old, but for others that might encounter the same problem...

                                      Check in your help file or reference manual, when you use inputsum, you can state a parameter whereby you call for how a value inputed at a parent level (in a pivot table) will be distributed through to the children, equal values, proprotionally, etc.

                                      Check "distribution modes" within inputsum. That might resolve the problem.