Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to allocate sales from the input field to the lower level in chart

Hello -

I have a chart with two dimensions, region and country. Country has a variable where a user has the option to display it or not. In the first example, the user only shows region and input sales to 'Europe' for example $20.

So it would look like this:

REGIONSum (SALES)InputSum(NPI)
18020
AMERICANS100
ASIA500
EUROPE12020

Once the user clicks 'Show Country' button, the sales from input field should allocate based on the sales in each country.

Example:

REGIONCOUNTRYSum (SALES)InputSum(NPI)
18020
AMERICANSUSA100
ASIACHINA500
EUROPEGERMANY30                    5
EUROPEITALY40                    7
EUROPEUK50                    8

But I don't get the correct allocation. $20 dollars that I input at region level is evenly distributed at the lowest level.

REGIONCOUNTRYSum (SALES)InputSum(NPI)
18020
AMERICANSUSA100
ASIACHINA500
EUROPEGERMANY30                    7
EUROPEITALY40                    7
EUROPEUK50                    7

Is it possible to distribute the input value based on the sales within the region? How would I do that?

Thanks for the help.

8 Replies
MK_QSL
MVP
MVP

May be any one from below

(Sum (SALES)/SUM(TOTAL <REGION> SALES) )*SUM(NPI)

or

(Sum (SALES)/SUM(TOTAL <REGION> SALES) )*NPI

or

(Sum (SALES)/SUM(TOTAL <REGION> SALES) )*INPUTSUM(NPI)

Not applicable
Author

This worked as I added a new column but would it be possible to do that within the same INPUTFIELD column. Using your function above, I get this

REGIONCOUNTRYSum (SALES)NPITEST
1803030
AMERICANSUSA1000
ASIACHINA5000
EUROPEGERMANY30103
EUROPEITALY40103
EUROPEUK50104

I used (Sum (SALES)/SUM(TOTAL <REGION> SALES) )*INPUTSUM(NPI) in TEST column but I would like to use that function within input field (NPI field). is that possible?

Thanks again!!!

Not applicable
Author

Manisa -

This worked but now i need to add more dimensions. I am trying to use lowest dimension to allocate the total but it is not working. So I added yet another dimension like this:

REGIONCOUNTRYSASum (SALES)NPINPI Adj
250212.00212.00
EUROPEGERMANYYUM3058.3358.33
EUROPEITALYBK4058.3325.93
EUROPEUKCC7025.0025.00
EUROPEUKIKEA5058.3358.33
ASIACHINABK506.003.33
AMERICANSUSAMCD106.006.00

But now the totals are not calculating correctly. I am using (Sum (SALES)/SUM(TOTAL <REGION> SALES) )*INPUTSUM(NPI) and changed the <REGION> to use <SA>. How do I make it work at the lowest level?

Thanks

MK_QSL
MVP
MVP

Sorry but didn't understood your problem.... Can you please let me know what exactly the output you require?

Not applicable
Author

Manisa -

This worked but now i need to add more dimensions. I am trying to use lowest dimension to allocate the total but it is not working. So I added yet another dimension like this:

REGIONCOUNTRYSASum (SALES)NPINPI Adj
250212.00212.00
EUROPEGERMANYYUM3058.3358.33
EUROPEITALYBK4058.3325.93
EUROPEUKCC7025.0025.00
EUROPEUKIKEA5058.3358.33
ASIACHINABK506.003.33
AMERICANSUSAMCD106.006.00

But now the totals are not calculating correctly. I am using (Sum (SALES)/SUM(TOTAL <REGION> SALES) )*INPUTSUM(NPI) and what I should see is this:

REGIONCOUNTRYSASum (SALES)NPINPI Adj% Adj
250212.00212.002
EUROPEGERMANYYUM3058.33322
EUROPEITALYBK4058.33422
EUROPEUKCC7025.00742
EUROPEUKIKEA5058.33532
ASIACHINABK506.006.002
AMERICANSUSAMCD106.006.002

So if I total all EUROPE NPI input field, I had 200 and that when summing sales is 190$ of which I need to allocate my NPI of 200. The correct NPI Adj is

REGIONCOUNTRYSANPI Adj
212.00
EUROPEGERMANYYUM32
EUROPEITALYBK42
EUROPEUKCC74
EUROPEUKIKEA53
ASIACHINABK6.00
AMERICANSUSAMCD6.00

Hope this helps.

THANK YOU!!!!

Not applicable
Author

Manish -

See the attached example.

Thank you again for all your help!

Not applicable
Author

Thank you I think I got it working.

Petra

fanko1225
Creator
Creator

Hi Petraraczynski,

I'm new to QV and i have the same problem with allocating input field values. I'm looking at your above $20 allocation post to Europe region and sample qvw and I couldnt figure it out how you get 5, 7, and 8 allocated base on their country, Can you provide steps how you resolve this? many thanks.

REGIONCOUNTRYSum (SALES)InputSum(NPI)
18020
AMERICANSUSA100
ASIACHINA500
EUROPEGERMANY305
EUROPEITALY407
EUROPEUK508

Frankie