How to divide all data in pivot table by use one value of them?

[#1]

Hi!!! All, I'm a newbie for Qlikview. And now I have a problem with the pivot table.

My case: I set the country as the dimension and make Sum(Sale) be the expression. The table look like this:

USA Sale = 100000000

Austria Sale = 12000000

Brazil Sale = 150000000

And I want to divide all Sale value(in every country) by use the sale value of Austria country.

I try to use set analysis and I use this code :

Sum (LineSalesAmount)/sum( {$<Country= {Austria} >} LineSalesAmount )

and the result shows just only the data in Austria country:

USA Sale = 120000000 Value divide by sale in Austria - ( this should be 1.2)

Austria Sale = 100000000 Value divide by sale in Austria 1

Brazil Sale = 150000000 Value divide by sale in Austria - ( this should be 1.5)

How can I use the data of Austria country divide all country?

Can we do that by use set analysis? Or can we do that by use the if function ( I try but not work too) ?

Could someone plese tell me the answer, thank you [:)]

[Oleg_Troyansky · Accepted solution]

You are almost there. Your Set Analysis condition helps to select Sales for Austria, however the chart expression is still calculated based on the Dimension, which is Country. In order to get Sales for Austria in your denominator, you need to break out of your dimensions. Use prefix TOTAL after the Set Analysis condition, and it will do the trick:

Sum (LineSalesAmount)/sum( {$<Country= {Austria} >} TOTAL LineSalesAmount )

welcome aboard!

[#3]

Thank you very much Oleg Troyansky!!! This very help me

[#4]

Hi,

This also can help you.

01 Make a variable like this:

Variable Name = ConVal

Variable Value = Sum(if(country='Austria',sales))

02. Make pivot table like this:

Dimension = country

Expression = sum(sales/ConVal)

thanks

zaman.

[#5]

Hi Oleg, First thank for your very

[#6]

Hello Oleg,

Thanks for your help.

I have the same problem with a chart pivot that has 2 dimensions (product + manufacturer).

your stuff works but the problem is that the TOTAL does not consider the first dimension.

What I want to do is to have one column with : Avg (price) / Avg({$<Manufacturer={'me}>} TOTAL price).

I have a pivot chart with product, manufacturer, expression.

The problem : for every manufacturer, the expression is the same. In fact, it must depend on the product.

Do you know if there is a function in which we should say : <Product=(currentproductintable)>

Thanks you !

[#7]

Hi Tatarjar,

You can also try this expression without set analysis.

= Sum(LineSalesAmount)/ Sum TOTAL ( If ( Country = 'Austria' , LineSalesAmount ) )

Regards,

Raj Kishor

[#8]

Hi Tatarjar,

You can also try this expression without set analysis.

= Sum(LineSalesAmount)/ Sum( TOTAL If ( Country = 'Austria' , LineSalesAmount ) )

Regards,

Raj Kishor

[#9]

Found it by myself.

The expression has to be Avg (price) / Avg({$<Manufacturer={'me}>} TOTAL <product> price) which means TOTAL within product dimension.

Cool !