Expression as share of total

[Anonymous]

Hello Community,

what I am trying to accomplish is to display the relative share of some value of a total. Here is a quick example:

• Suppose I have companies as my dimension.
• Then I have individual products (Product A, B and C) sold by all the companies.
• In a bar chart (or basically any chart), I want to display the share of one product relative to total sales (i.e. of all products) of each company as the expression.
• Lets say one example company has sold 100 units of each product. Then I want the bar representing this company to display 0.33 when I select a specific product.

This seems trivial, but the problem is that total sales are not included in the selection anymore when I choose a specific product.

For example, when I have product A selected, then SUM(Sales)/SUM(TOTAL Sales) will display the share of sales of product A for each company relative to total sales of product A (across all companies combined), rather than share of total sales of each company.

SUM(Sales)/SUM(TOTAL <Company> Sales) will show the share of selected products relative to each other, e.g. when I select products A and B, and each one was sold 100 times, the formula will show 0.5 for each, instead of 0.66.

How can I achieve that the formula refers to the total sales of all products by each company?

Thanks a lot!

[swuehl]

Try a combination with set analysis:

SUM(Sales)/SUM({<Product= >} TOTAL<Company> Sales)

[Anonymous]

Hi Stefan,

could you please elaborate a bit further on your suggestion? What exactly is the bold expression doing compared to the rest?

[Anonymous]

Unfortunately, it also does not work. It says no data to display...  Adding a specific product after the = sign in your bold expression ( {<Product = 'A'>} ) also does not work.

Kind regards,

Martin Müller

[avinashelite]

{<Product= >}

will not consider any selection made on the Product filter i.e. it will ignore the Product selection while calculating the sum of sales

[swuehl]

It's always helpful to look at a specific sample to get the syntax right and also to state the context and expected result.

A test data set for example could look like:

LOAD * INLINE [

Product,Company, Units

ProdA, CompA, 100

ProdB, CompA, 100

ProdC, CompA, 100

ProdA, CompB, 100

ProdB, CompB, 200

ProdC, CompB, 300

];

Then a table chart with no selections could look like

Company	Product	Sum(Units)	Sum(TOTAL<Company> Units)	Sum( Units) / Sum({<Product= >}TOTAL<Company> Units)
		900	900	100%
CompA	ProdA	100	300	33%
CompA	ProdB	100	300	33%
CompA	ProdC	100	300	33%
CompB	ProdA	100	600	17%
CompB	ProdB	200	600	33%
CompB	ProdC	300	600	50%

And with ProdA selected in field Product:

Company	Product	Sum(Units)	Sum(TOTAL<Company> Units)	Sum( Units) / Sum({<Product= >}TOTAL<Company> Units)
		200	200	22%
CompA	ProdA	100	100	33%
CompA	ProdB	0	100	0%
CompA	ProdC	0	100	0%
CompB	ProdA	100	100	17%
CompB	ProdB	0	100	0%
CompB	ProdC	0	100	0%

Or using only Company as dimension:

Company	Sum(Units)	Sum(TOTAL<Company> Units)	Sum( Units) / Sum({<Product= >}TOTAL<Company> Units)
	200	200	22%
CompA	100	100	33%
CompB	100	100	17%

[Anonymous]

Thanks for your help. In the end I used a slightly different expression, but your hint towards set analysis led me to this presentation, which helped a lot to understand the syntax and methodology behind it.

Best,

Martin

[Anonymous]

Learning Set Analysis from Scratch‌

https://community.qlik.com/servlet/JiveServlet/download/245786-42707/QlikLearn-Set-Analysis.pdfLearning Set Analysis from Scratch