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

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

wizardo — Wed, 22 Apr 2009 12:31:52 GMT

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

Wed, 22 Apr 2009 14:41:12 GMT

Hello Mansyno,

see the attached exmple.

Rainer

WhatIF scenario using inputfield in a pivot with Percentage

wizardo — Thu, 23 Apr 2009 05:28:30 GMT

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 — Thu, 23 Apr 2009 08:06:16 GMT

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

wizardo — Fri, 24 Apr 2009 22:54:08 GMT

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 — Fri, 24 Apr 2009 23:13:54 GMT

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

wizardo — Fri, 24 Apr 2009 23:29:13 GMT

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

wizardo — Wed, 29 Apr 2009 01:19:20 GMT

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 — Wed, 29 Apr 2009 02:49:36 GMT

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 ?

WhatIF scenario using inputfield in a pivot with Percentage

wizardo — Wed, 29 Apr 2009 04:03:16 GMT

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

wizardo — Mon, 04 May 2009 00:29:13 GMT

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

Mon, 04 May 2009 05:03:55 GMT

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

wizardo — Mon, 04 May 2009 13:06:12 GMT

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

Tue, 05 May 2009 05:42:34 GMT

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

wizardo — Tue, 05 May 2009 19:40:52 GMT

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

WhatIF scenario using inputfield in a pivot with Percentage

wizardo — Sun, 17 May 2009 02:02:17 GMT

I apologize for doing this agan but i promise its the last time, if the post gets buried again i will close it for lack of public intrest:)

any new ideas will be apprichated

thanks

Wizardo (aka Mansyno)

WhatIF scenario using inputfield in a pivot with Percentage

Oleg_Troyansky — Mon, 18 May 2009 00:32:21 GMT

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:

instead of:

[Total] * [% change]

use

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...

Oleg

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

Tue, 22 Dec 2009 00:31:22 GMT

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.