topic Re: Divide some value of same column. in QlikView

Divide some value of same column.

Thu, 25 Jul 2013 13:58:17 GMT

Hi all

I have this pivot table:

In the column "Total" i'd like to divide each partial value of "Quota di Mercato" by Country, with total value of "Quota di Mercato" by Division.
For example:
- In corrispondence of "Belgium" i'd like to have: 0,09% / 2,11%

- In corrispondence of "Germany": 0,85% / 2,11%

- and so on...

I'm still not very good with the QlikView sintax expression, so i need help

Re: Divide some value of same column.

daveamz — Thu, 25 Jul 2013 14:13:13 GMT

Hi,

To calculate the total of Quota di Mercato by division you can use sum(total <Division> Data).

Regards,

David

Re: Divide some value of same column.

Fri, 26 Jul 2013 08:01:24 GMT

I try to explain better:

I have this table where there are Product, Division and Country.

The expression to calculate "Vendite" (Sales) is the following:

=Sum(LineSalesAmount)

The column "Quota di mercato" (Market Share) is calculated as follow:

=Sum(LineSalesAmount) / Sum(Total LineSalesAmount) * 100

Now, i have to calculate "Indice di penetrazione" (Penetration Index).
The math formula is this :

PenetrationIndex = (CountryMarketShare / DivisionMarketShare) * 100

Can someone help me?

Re: Divide some value of same column.

swuehl — Fri, 26 Jul 2013 09:07:34 GMT

As Daveamz01 mentioned, you can add a field list to the TOTAL qualifier, stating the dimensions that still need to be considered in your aggregation. So your Index could probably calculated like

=Sum(TOTAL<ProductName, DivisionName> LineSalesAmount) / Sum(TOTAL LineSalesAmount)

Re: Divide some value of same column.

Gysbert_Wassenaar — Fri, 26 Jul 2013 09:10:27 GMT

Something like:

CountryMarketShare / (sum(total <ProductName> LineSalesAmount)/sum(total LineSalesAmount))

see attached example.

Re: Divide some value of same column.

Fri, 26 Jul 2013 09:32:00 GMT

Thank you, it works perfectly