!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)

[wizardo]

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

[#3]

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

[wizardo]

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

[#5]

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

[wizardo]

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

[wizardo]

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)

[Oleg_Troyansky]

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

[#9]

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.