Re: How to allocate sales from the input field to ... - Qlik Community

Report Inappropriate Content · ‎2014-08-08

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:

REGION	Sum (SALES)	InputSum(NPI)
	180	20
AMERICANS	10	0
ASIA	50	0
EUROPE	120	20

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

Example:

REGION	COUNTRY	Sum (SALES)	InputSum(NPI)
		180	20
AMERICANS	USA	10	0
ASIA	CHINA	50	0
EUROPE	GERMANY	30	5
EUROPE	ITALY	40	7
EUROPE	UK	50	8

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

REGION	COUNTRY	Sum (SALES)	InputSum(NPI)
		180	20
AMERICANS	USA	10	0
ASIA	CHINA	50	0
EUROPE	GERMANY	30	7
EUROPE	ITALY	40	7
EUROPE	UK	50	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.

MK_QSL · ‎2014-08-09

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)

Report Inappropriate Content · ‎2014-08-09

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

REGION	COUNTRY	Sum (SALES)	NPI	TEST
		180	30	30
AMERICANS	USA	10	0	0
ASIA	CHINA	50	0	0
EUROPE	GERMANY	30	10	3
EUROPE	ITALY	40	10	3
EUROPE	UK	50	10	4

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

Report Inappropriate Content · ‎2014-08-10

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:

REGION	COUNTRY	SA	Sum (SALES)	NPI	NPI Adj
			250	212.00	212.00
EUROPE	GERMANY	YUM	30	58.33	58.33
EUROPE	ITALY	BK	40	58.33	25.93
EUROPE	UK	CC	70	25.00	25.00
EUROPE	UK	IKEA	50	58.33	58.33
ASIA	CHINA	BK	50	6.00	3.33
AMERICANS	USA	MCD	10	6.00	6.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 · ‎2014-08-10

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

Report Inappropriate Content · ‎2014-08-10

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:

REGION	COUNTRY	SA	Sum (SALES)	NPI	NPI Adj
			250	212.00	212.00
EUROPE	GERMANY	YUM	30	58.33	58.33
EUROPE	ITALY	BK	40	58.33	25.93
EUROPE	UK	CC	70	25.00	25.00
EUROPE	UK	IKEA	50	58.33	58.33
ASIA	CHINA	BK	50	6.00	3.33
AMERICANS	USA	MCD	10	6.00	6.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:

REGION	COUNTRY	SA	Sum (SALES)	NPI	NPI Adj	% Adj
			250	212.00	212.00	2
EUROPE	GERMANY	YUM	30	58.33	32	2
EUROPE	ITALY	BK	40	58.33	42	2
EUROPE	UK	CC	70	25.00	74	2
EUROPE	UK	IKEA	50	58.33	53	2
ASIA	CHINA	BK	50	6.00	6.00	2
AMERICANS	USA	MCD	10	6.00	6.00	2

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

REGION	COUNTRY	SA	NPI Adj
			212.00
EUROPE	GERMANY	YUM	32
EUROPE	ITALY	BK	42
EUROPE	UK	CC	74
EUROPE	UK	IKEA	53
ASIA	CHINA	BK	6.00
AMERICANS	USA	MCD	6.00

Hope this helps.

THANK YOU!!!!

Report Inappropriate Content · ‎2014-08-10

Manish -

See the attached example.

Thank you again for all your help!

Report Inappropriate Content · ‎2014-08-11

Thank you I think I got it working.

Petra

Anonymous · ‎2015-03-20

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.

REGION	COUNTRY	Sum (SALES)	InputSum(NPI)
		180	20
AMERICANS	USA	10	0
ASIA	CHINA	50	0
EUROPE	GERMANY	30	5
EUROPE	ITALY	40	7
EUROPE	UK	50	8

Frankie

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