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

[wizardo]

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)

[#2]

Hello Mansyno,

see the attached exmple.

Rainer

[wizardo]

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

[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

[wizardo]

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

[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

[wizardo]

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

[wizardo]

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

[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 ?

[wizardo]

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