Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor

% Sales contribution Wrt first dimension

Dear community,

Below are my input and Output tables.. Please suggest the best way to achieve this -

Input -

 

RegionSub-RegionSales
APACChina123
APACIndia521
APACPakistan214
LATAMBrazil302
LATAMArgentina325
LATAMUruguay142

Output -

- Region and Sub-region are taken as dimensions

- Chart type - pivot table

- %sales contribution formula (for china let's say) = 123/(123+521+214), in other words - Sum of sales of sub-region / Total sales of the respective region

Thanks in advance

-Sneh

1 Solution

Accepted Solutions
Highlighted
Esteemed Contributor II

Re: % Sales contribution Wrt first dimension

Sum (Sales)/Sum (Total <Region> Sales)

View solution in original post

8 Replies
Highlighted
New Contributor III

Re: % Sales contribution Wrt first dimension

try to add  %sales contribution /region  as calculated dimension (instead of expression).

Highlighted
Valued Contributor

Re: % Sales contribution Wrt first dimension

Hi

Please find the attached app for your reference.

Regards

Pratyush

Highlighted
Honored Contributor II

Re: % Sales contribution Wrt first dimension

Hi,

PFA, Try like below,

Sales Contribution %

=Sum(Sales)/aggr(sum(Sales),Region)

Thanks, Deva

Highlighted
Partner
Partner

Re: % Sales contribution Wrt first dimension

Hi,

Try this expression---

sum(Sales)/sum(TOTAL<Region> Sales)

Highlighted
Esteemed Contributor II

Re: % Sales contribution Wrt first dimension

Sum (Sales)/Sum (Total <Region> Sales)

View solution in original post

Highlighted
Valued Contributor III

Re: % Sales contribution Wrt first dimension

Hello Snehasis,

Trust that you are doing great!

To fulfill your requirement follow below given steps:

1. Create new chart as Pivot table (Title - Net Sales).

2. Add Region & Sub-Region dimensions from Available Fields/Groups to Used Dimensions.

3. Next, add Net Sales expression i.e. Sum(Sales)

4. Post that add, % Sales Contribution/Region expression definition as below:

    Sum(Sales) / Sum(TOTAL <Region> Sales)

    Sum(Sales) - will give total sales for that Region and Sub-Region

    Sum(TOTAL <Region> Sales) - will generate Region level Sales total

5. For % Sales Contribution/Region show in Percent (%) settings under Number tab.

Please refer sample application attached herewith.

Regards!

Rahul

Highlighted
Contributor

Re: % Sales contribution Wrt first dimension

Thanks Deva! In Fact, I was also using the same expression. Do not know why it gets null values at some places.

Vineeth's solution works perfectly though.

Highlighted
Contributor

Re: % Sales contribution Wrt first dimension

Thanks a lot Vineeth, this works just fine. I had totally forgot about Total..