Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

sales percentage calculation

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

3 Replies
Employee
Employee

Re: sales percentage calculation

Maybe this

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

Not applicable

Re: sales percentage calculation

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!

MVP
MVP

Re: sales percentage calculation

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).

Community Browser