Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You are using the Month as additional dimension to your table?
Then try:
=sum(Sales) / sum(total<Month> Sales)
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
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.
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.
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
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?
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?
You are using the Month as additional dimension to your table?
Then try:
=sum(Sales) / sum(total<Month> Sales)
That works perfectly. thank you for your swift responses.