Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]))
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])))
Add the set modifier to the avg functions too.
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])))
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])
Thank you Sunny, breaking down the script worked.
Thanks for the brilliant idea