topic Re: sales percentage calculation in QlikView

sales percentage calculation

Fri, 13 Jun 2014 21:11:53 GMT

Hi there,

I am trying to calculate the percentage of each country's sales on the total sales. I put the expression like this:

=num(if(CurrencyCode='USD',SUM(Sale),sum(Sale/Rate))/if(CurrencyCode='USD',sum({<Country=>}Sale),sum({<Country=>}Sale/Rate)),'##.00%')

But it returns to me 100% for each country. Can anyone tell me what goes wrong? Thank you so much!

Best Regards,

Gloria

Re: sales percentage calculation

Clever_Anjos — Fri, 13 Jun 2014 21:14:33 GMT

Maybe this

=num(if(CurrencyCode='USD',SUM(Sale),sum(Sale/Rate))/if(CurrencyCode='USD',sum({<Country=>}Sale),sum(total Sale/Rate)),'##.00%')

Re: sales percentage calculation

Fri, 13 Jun 2014 21:19:52 GMT

I don't have a column of total sale,that's the thing. Each part works well when I calculate them. It just doesn't work when I am trying to divide one by the other. Thank you!

Re: sales percentage calculation

swuehl — Sat, 14 Jun 2014 08:47:54 GMT

I think you'll need the TOTAL qualifier here, to ignore your Country dimension.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/09/09/aggregation-scope

=num(

if(CurrencyCode='USD',

sum(Sale) / sum (TOTAL Sale),

sum(Sale/Rate) / sum(TOTAL Sale/Rate)

)

,'##.00%')

If you are using multiple dimensions and you want to ignore Country, but not Branch, use a field list with your qualifier:

=num(

if(CurrencyCode='USD',

sum(Sale) / sum (TOTAL<Branch> Sale),

sum(Sale/Rate) / sum(TOTAL<Branch> Sale/Rate)

)

,'##.00%')

Note: I assumed thye you are using a chart with dimension Country here, but this information should be part of your request.

I also assumed more things, like CurrencyCode is a field that has an unambiguous value per Country.

For your next request, please add as many information as possible, this is most easy when upload a simple demo QV application that shows what you want to achieve (i.e. a data model close to your original one and the expected outcome).