How to divide all data in pivot table ?

Thu, 20 May 2010 11:35:39 GMT

Hello all, from my last post about topic How to divide all data in pivot table by use one value of them? (http://community.qlik.com/forums/p/29143/112079.aspx#112079)

Now I can divide all values by use set analysis. And I try to use the set analysis to divide data in two dimensions.

I add the Year dimension into the table.

Now, the data divide by sum Sale in Austria(All year).

If I want the data divide by sum Sale in Austria(All year) when I see the data only in Country dimension. And the data divide by sum Sale in Austria(specify year) when I see the data in second dimension(Year).

Such as: USA in all year / Austria in all year ,USA in 2003 / Austria in 2003 ,Spain in 2004 / Austria in 2004

So the result of Austria in Sum column should be 1 in every year.

I solve this problem by use if function.

Sum(LineSalesAmount)/if(Country<>null and Year ='2002' ,sum({$<Country={Austria}, Year={2002}>} total LineSalesAmount),
if(Country<>null and Year ='2003' ,sum({$<Country={Austria}, Year={2003}>} total LineSalesAmount),
if(Country<>null and Year ='2004' ,sum({$<Country={Austria}, Year={2004}>} total LineSalesAmount),
if(Country<>null and Year ='2005' ,sum({$<Country={Austria}, Year={2005}>} total LineSalesAmount),
if(Country<>null and Year ='2006' ,sum({$<Country={Austria}, Year={2006}>} total LineSalesAmount),
if(Country<>null and Year ='2007' ,sum({$<Country={Austria}, Year={2007}>} total LineSalesAmount),sum({$<Country={Austria}>} total LineSalesAmount)))))))

And its can solve my problem, but because the data is too big. So this method will not good.

Can I solve the problem by use another function?

I try to find the way to divide data like this, but I still don't know how. Or we can just use if function for this problem?

I would be more than happy if someone could give me a hint. Thanks!

How to divide all data in pivot table ?

Thu, 20 May 2010 14:42:42 GMT

Please provide small QVW with data, and your expected result, so that some one can give you better solution.

Thank you

How to divide all data in pivot table ?

Tue, 08 Jun 2010 16:25:10 GMT

Now I can solve the problem by use this expression :

topic How to divide all data in pivot table ? in QlikView

How to divide all data in pivot table ?

How to divide all data in pivot table ?

How to divide all data in pivot table ?

sum(LineSalesAmount)/sum({<Country={Austria}>} total<Year> LineSalesAmount)