Making new Categories and calculationg Ratios

[#1]

Dear Ladies and Gentlemen,

I've the following problem:

Saler Order No	Sales Order Item No	Main Article Group	Product	Units	Price	Region	Country
10101001	10	Cars	4 Wheels	19	100.000	North America	USA
10101001	20	Bus	40 tons	1	200.203	North America	Canada
1012222	70	Bike	3 Wheels	100	1.000.000	EMEA	France
1009999	10	Cart	Small	11	50.00	EMEA	UK
255583	80	Cart	Small	1	7.00	EMEA	Denmark
566666	10	Cart	BIG	3004	10.000	APAC	China

I've the Main Article Group and the Product as Listboxes.

In a chart my Main Article Group is a Dimension. But the original Main Article Group so calculate by a ratio. E.g.

Cart = Cart + Cart*BIG/Small + 4Wheels/ Total 4 Wheels.

The ratios are different by Regio and by Country.

I've solved this with aggr() and Set Analysis. The Problem is, when I select a Main Article Group in the List boxes or a Product,

the Calculation of the ratio is not correct. - Obviously, after Selection, Qlikview does not have the needed values.

How can I avoid this? How can I still drill down from Main Article Group to a Product?

Thank you for your help.

[#2]

can anybody help me? it's so urgent.

Thank you in advance.

[rwunderlich]

You probably need to use some combination of set indentifiers {1} and possibly TOTAL keywords to enlarge your set for the ratio calc.

-Rob

[#4]

Thank you Rob for your answer. Can you please give me an detailed example to better understand?

[rwunderlich]

Post an example qvw and perhaps we can demonstrate. You can scramble your data to protect it.

-Rob

[#6]

So the major issue is: when selecting an MAG via a Listbox, the calculated MAGs are no longer show properly, as

the values in the ratios are no longer in the dataset (selection).

So the opportunity would be to do the calculation in the script.

The problem is, I need the ratios per month, per year, per geo4, per country. This is acctually the AGGR().

If I'm going to this in the datamodel, how could be the datastructre?

is this the right approach?

[rwunderlich]

I was suggesting using the {1} set identifier like this.

=sum(value) / sum({1} value)

That would make the divisor ignore selections.

-Rob

[#8]

Hi Rob,

thanks for your answer. I was trying to do this with {1}, but did not succeed.

Were shall I put it?

The divisor is a multiplication.