7 Replies Latest reply: Dec 4, 2012 10:52 AM by Daniel Wewerka

# Making new Categories and calculationg Ratios

I've the following problem:

Saler Order NoSales Order Item No
Main Article GroupProductUnitsPriceRegionCountry
1010100110Cars4 Wheels19100.000North AmericaUSA
101222270Bike3 Wheels1001.000.000EMEAFrance
100999910CartSmall1150.00EMEAUK
56666610CartBIG300410.000APACChina

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?

• ###### Making new Categories and calculationg Ratios

can anybody help me? it's so urgent.

• ###### Making new Categories and calculationg Ratios

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

-Rob

• ###### Making new Categories and calculationg Ratios

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

• ###### Making new Categories and calculationg Ratios

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

-Rob

• ###### Making new Categories and calculationg Ratios

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?

• ###### Making new Categories and calculationg Ratios

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

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

That would make the divisor ignore selections.

-Rob

• ###### Re: Making new Categories and calculationg Ratios

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.