Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Percentages not adding up to 100% in bar chart

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

1 Solution

Accepted Solutions
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])))

View solution in original post

5 Replies
Gysbert_Wassenaar

Add the set modifier to the avg functions too.


talk is cheap, supply exceeds demand
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])))

vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

Thank you Sunny, breaking down the script worked.

Not applicable
Author

Thanks for the brilliant idea