Sometimes, it may be necessary to display percentages for each item of dimension 1 in a way to add up to 100%, as in the following screenshot:

The solution is to use aggregation in the expression which calculates percentages:

expr 1: Total Sales: sum(SalesAmount)

expr 2: Percentage: sum(SalesAmount)/aggr(NODISTINCT sum(SalesAmount),Country)

The applied solution can be viewed in the qlikview application which can be downloaded from the following link:

https://sites.google.com/site/quickdevtips/home/SecondDimPercentage.qvw

I have a question. In your example, how can I calculate the average of the percentages?

ReplyDeleteWhat I need is a table with Column Channel and the average of the values.

The first column would be

Online | 51 %

etc

51=(45.83+39.47+43.75+100+53.13+23.81)/6

This comment has been removed by the author.

ReplyDeleteI have a simpler solution

ReplyDeletesum(SalesAmount)/sum(TOTAL<Country> SalesAmount)

It doesn't work when you have the calculated dimensions basing on aggr function.

Deletehello, how can I calculate the max of the percentages in all groups?

ReplyDeleteHi,

DeletePlease try the following expression:

=max(aggr(sum(SalesAmount),Country,Channel)) / sum(TOTAL SalesAmount)

very useful post, how can I get accumulated percentages per dimension? on your sample Canada Online would show 45.83, Canada Retail would show 77.08 and telemarketting would show 100

ReplyDeleteplease change the numerator of the percentage calculation as "rangesum(Above(sum(SalesAmount),0,NoOfRows()))"

DeleteThis comment has been removed by the author.

ReplyDeleteAwesome Post . Thanks !! Can you please tell me how to have the same on a chart , except with the actual numbers shown in the bars . Something like the below ?

ReplyDelete________________________________________________________________

| | | |

|___2_____|_____________20_______________________|_____5__________|

0% 50% 100%

Hi Aydin,

ReplyDeleteI faced as similar kind of situation where I have a second dimension as calculated dimension and was attempting to get percentages grouped by first dimension , I tired ur aggr with nodistinct and It provided me the solution but to my surprise when I am selecting a filter the table is showing only the selected value percentages rather than showing all in the list and I am also using p() function.

Dimension: Commodity

if(GetSelectedCount(Supplier) > 0,

if(

AGGR(RANK(Sum({1-$}Spend)),[Commodity],[Supplier])<=5

OR

AGGR(RANK(Sum({$}Spend)),[Commodity],[Supplier])<=5,

Supplier)

,

if(AGGR(RANK(Sum({1}Spend)),[Commodity],[Supplier])<=5, Supplier)

)

The use of this dimension is , it will return selected supplier + top 5 suppliers grouped by commodity.

Expressions:

Spend:

Sum({1}Spend)

MarketShare:

Sum({1}Spend)

/

Aggr(NODISTINCT Sum({1}Spend),Commodity)

Savings:

Sum({1}[MPN Savings])

/

Sum({1}Spend)

The second expression is working fine when nothing is selected in supplier filter but when selected it is showing only selected supplier percentage.

Any help??

got solution by adding a max() function to aggr() function

ReplyDeleteSum({1}Spend)

/

Max({1} aggr( NODISTINCT Sum({1}Spend),Commodity))

ReplyDeleteSum({1}Spend)

/

Max({1} aggr( NODISTINCT Sum({1}Spend),Commodity))

Max({1} aggr( NODISTINCT ...)

ReplyDelete