5 Replies Latest reply: Jan 4, 2017 1:34 PM by Monica Chhabra RSS

    Percentages not adding up to 100% in bar chart

    Monica Chhabra

      Hi All,

       

      I have created a bar chart in qlik sense using a single table in data model.  The data model has Product Name,  Sales Amount, Quote ID, Seller Role.  There are duplicate Sales Amount and other fields for unique Quote ID.  I am creating a bar chart for comparison of % Sales Amount for Basic, Essential and Complete Product Name by Month.  I have created 3 measures (MSE Basic, MSE Essential and MSE Complete) over Date dimension.

      Problem I have is percentages are not adding up to 100% in the bar chart.  Please see below syntax and bar chart from visualization attached.

      Can someone help me with the issue.

       

      Thanks you,

      Monica

       

      Measure for Basic:

      Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Basic'}>}Aggr(Avg([Sales Amount]), [Quote ID]))/

      Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Basic', 'Essential', 'Complete'}>}Aggr(Avg([Sales Amount]), [Quote ID]))

       

      Measure for Essential:

      Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Essential'}>}Aggr(Avg([Sales Amount]), [Quote ID]))/

      Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Basic', 'Essential', 'Complete'}>}Aggr(Avg([Sales Amount]), [Quote ID]))

       

      Measure for Complete:

      Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Complete'}>}Aggr(Avg([Sales Amount]), [Quote ID]))/

      Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Basic', 'Essential', 'Complete'}>}Aggr(Avg([Sales Amount]), [Quote ID]))

        • Re: Percentages not adding up to 100% in bar chart
          Gysbert Wassenaar

          Add the set modifier to the avg functions too.

          • Re: Percentages not adding up to 100% in bar chart
            Sunny Talwar

            Start with breaking this down like this

             

            Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Basic'}>}Aggr(Avg([Sales Amount]), [Quote ID])) +

            Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Essential'}>}Aggr(Avg([Sales Amount]), [Quote ID])) +

            Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Complete'}>}Aggr(Avg([Sales Amount]), [Quote ID]))


            =


            Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Basic', 'Essential', 'Complete'}>}Aggr(Avg([Sales Amount]), [Quote ID]))


            If these two are not equal, then you are never going to get 100% total for the three bars. May be in that case, you can try this:


            Basic

            Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Basic'}>}Aggr(Avg([Sales Amount]), [Quote ID]))/

            (Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Basic'}>}Aggr(Avg([Sales Amount]), [Quote ID])) +

            Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Essential'}>}Aggr(Avg([Sales Amount]), [Quote ID])) +

            Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Complete'}>}Aggr(Avg([Sales Amount]), [Quote ID])))


            Essential

            Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Essential'}>}Aggr(Avg([Sales Amount]), [Quote ID]))/

            (Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Basic'}>}Aggr(Avg([Sales Amount]), [Quote ID])) +

            Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Essential'}>}Aggr(Avg([Sales Amount]), [Quote ID])) +

            Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Complete'}>}Aggr(Avg([Sales Amount]), [Quote ID])))

             

            Complete

            Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Complete'}>}Aggr(Avg([Sales Amount]), [Quote ID]))/

            (Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Basic'}>}Aggr(Avg([Sales Amount]), [Quote ID])) +

            Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Essential'}>}Aggr(Avg([Sales Amount]), [Quote ID])) +

            Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Complete'}>}Aggr(Avg([Sales Amount]), [Quote ID])))

            • Re: Percentages not adding up to 100% in bar chart
              Vineeth Pujari

              1) Why Don't you have a separate table for Distinct QuoteID's and Sales Amount? to avoid AGGR(AVG()) altogether.

                        Load Distinct QuoteID

                                  Min(Sales Amount) as Sales Amount

                             Resident YourTable

                        Group by QuoteID

               

               

               

              2) Instead of having 3 Expressions why not have just 1 Expression

                   with Month_Year and ProductName as dimension

              and Expression like

                 =Sum({$<[Seller Role] = {'MSE'}, [Product Name] = {'Basic','Essential','Complete'} >}[Sales Amount])

                                       /

                  Sum(TOTAL {$<[Seller Role] = {'MSE'}, [Product Name] = {'Basic','Essential','Complete'} >} [Sales Amount])