topic Re: % Sales contribution Wrt first dimension in QlikView

% Sales contribution Wrt first dimension

ashish_2511 — Wed, 25 Nov 2020 16:16:04 GMT

Dear community,

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

Input -

Region	Sub-Region	Sales
APAC	China	123
APAC	India	521
APAC	Pakistan	214
LATAM	Brazil	302
LATAM	Argentina	325
LATAM	Uruguay	142

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

Re: % Sales contribution Wrt first dimension

gowtham_patnaik — Mon, 13 Feb 2017 05:49:34 GMT

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

Re: % Sales contribution Wrt first dimension

prat1507 — Mon, 13 Feb 2017 05:58:01 GMT

Please find the attached app for your reference.

Regards

Pratyush

Re: % Sales contribution Wrt first dimension

devarasu07 — Mon, 13 Feb 2017 06:06:40 GMT

Hi,

PFA, Try like below,

Sales Contribution %

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

Thanks, Deva

Re: % Sales contribution Wrt first dimension

miskin_m — Mon, 13 Feb 2017 06:06:43 GMT

Hi,

Try this expression---

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

Re: % Sales contribution Wrt first dimension

vinieme12 — Mon, 13 Feb 2017 06:07:52 GMT

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

Re: % Sales contribution Wrt first dimension

rahulpawarb — Mon, 13 Feb 2017 06:16:37 GMT

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

Re: % Sales contribution Wrt first dimension

ashish_2511 — Mon, 13 Feb 2017 06:17:38 GMT

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.

Re: % Sales contribution Wrt first dimension

ashish_2511 — Mon, 13 Feb 2017 06:20:08 GMT

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