Amending percentages based on selections

[#1]

Hi,

I'm new to Qlikview and I'm trying to work out the best way to show my data.

I have 2 sets of data which I've concatenated, both sets contain the same data but for different locations. They both show a percentage of sales within that location, which is fine when I select a location but doesn't work when the selections are clear.

I want to show the sales percentage as an overall percentage of the total sales.

My Data would be structured something like this.

Company	Product	Location	Sales	Sales Share
A	1	England	10	7.14%
B	1	England	20	14.29%
C	1	England	15	10.71%
D	1	England	20	14.29%
A	2	England	35	25.00%
B	2	England	25	17.86%
C	2	England	10	7.14%
D	2	England	5	3.57%
Company	Product	Location	Sales	Sales Share
A	1	Scotland	5	2.94%
B	1	Scotland	10	5.88%
C	1	Scotland	20	11.76%
D	1	Scotland	25	14.71%
A	2	Scotland	20	11.76%
B	2	Scotland	40	23.53%
C	2	Scotland	30	17.65%
D	2	Scotland	20	11.76%

The outcome I would want would be as below, however I am getting the Sales Share as a SUM of the two subsets, rather than a product.

Company	Product	Location	Sales	Sales Share                   Actual
A	1	Combined	15	4.84%	10.08%
B	1	Combined	30	9.68%	20.17%
C	1	Combined	35	11.29%	22.48%
D	1	Combined	45	14.52%	28.99%
A	2	Combined	55	17.74%	36.76%
B	2	Combined	65	20.97%	41.39%
C	2	Combined	40	12.90%	24.79%
D	2	Combined	25	8.06%	15.34%

If somebody could help me with this issue, I would be extremely grateful.

Regards,
Robert

[swuehl]

Hi Robert,

hope I got your setting correctly.

What if you try putting as dimension only Company and Product, then as expression1

=sum(Sales)

and as expression2 to calculate the share

=sum(Sales) / sum(total Sales)

?

Hope this helps,

Stefan

[#3]

Thanks Stefan,

if you assume I'm a complete novice at this (I am!) then can you explain how I would calculate the total sales? I don't currently have a total sales record and this would therefore only return 100%?

Please correct me if i'm wrong.

[#4]

Thanks Stefan,

if you assume I'm a complete novice at this (I am!) then can you explain how I would calculate the total sales? I don't currently have a total sales record and this would therefore only return 100%?

Please correct me if i'm wrong.

[swuehl]

Hi Rob,

TOTAL is qualifier to the sum function in this case, a QlikView keyword. You don't need a total sales field (and if, you would need to say [total sales] for a field with a space in the name).

The TOTAL qualifier disregards the charts dimension, thus summing all your values (but taken selection into account).

Hope this helps,

Stefan

[#6]

Thanks Stefan,

This works fine, I just have one more complication, I have the data by month, how would I get it to calculate based on the total sales for each month?

[#7]

Thanks Stefan,

This works fine, I just have one more complication, I have the data by month, how would I get it to calculate based on the total sales for each month?

[swuehl · Accepted solution]

You are using the Month as additional dimension to your table?

Then try:

=sum(Sales) / sum(total<Month> Sales)

[#9]

That works perfectly. thank you for your swift responses.