3 Replies Latest reply: Jun 14, 2014 4:47 AM by Stefan Wühl RSS

    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

        • Re: sales percentage calculation
          Clever Anjos

          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
            Stefan Wühl

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