Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Amending percentages based on selections

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.

CompanyProductLocationSalesSales Share
A1England107.14%
B1England2014.29%
C1England1510.71%
D1England2014.29%
A2England3525.00%
B2England2517.86%
C2England107.14%
D2England53.57%
CompanyProductLocationSalesSales Share
A1Scotland52.94%
B1Scotland105.88%
C1Scotland2011.76%
D1Scotland2514.71%
A2Scotland2011.76%
B2Scotland4023.53%
C2Scotland3017.65%
D2Scotland2011.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.

CompanyProductLocationSalesSales Share                   Actual
A1Combined154.84%  10.08%
B1Combined309.68%20.17%
C1Combined3511.29%22.48%
D1Combined4514.52%28.99%
A2Combined5517.74%36.76%
B2Combined6520.97%41.39%
C2Combined4012.90%24.79%
D2Combined258.06%15.34%

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

Regards,
Robert

1 Solution

Accepted Solutions
MVP
MVP

Amending percentages based on selections

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

Then try:

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

8 Replies
MVP
MVP

Amending percentages based on selections

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

Not applicable

Amending percentages based on selections

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.

Not applicable

Re: Amending percentages based on selections

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.

MVP
MVP

Amending percentages based on selections

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

Not applicable

Amending percentages based on selections

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?

Not applicable

Re: Amending percentages based on selections

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?

MVP
MVP

Amending percentages based on selections

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

Then try:

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

Not applicable

Re: Amending percentages based on selections

That works perfectly. thank you for your swift responses.

Community Browser