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
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 ?
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:
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
* in this example im asuming the use of the default distribution mode of the inputAvg function like this: inputAvg(Inc)
Does it make sense ?
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?
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.
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.
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.
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
WhatIF_Pivot.qvw 182.3 K
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.
thanks for the reply
"......., 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
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.
WhatIF_Pivot_Solution.qvw 320.5 K
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
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.
WhatIF_Pivot_Solution.qvw 281.5 K
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
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)
its also to bad that there isnt an option to mark an post as partialy answering the question thread :) lol
The only other suggestion I can offer is try the same, only using the original fields for the "Simulated calculation" instead of referencing the columns - it is usually more accurate for subtotals:
[Total] * [% change]
sum(Original Amount * (1+Change))
See attached. If this doesn't help - sorry, my friend, I guess not every problem can be solved in a Community Forum...
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.