05 May 2012

Qlikview: Aggregation 1: How to calculate percentages at second dimension level

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


14 comments:

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

    What 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

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. I have a simpler solution
    sum(SalesAmount)/sum(TOTAL<Country> SalesAmount)

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

      Delete
  4. hello, how can I calculate the max of the percentages in all groups?

    ReplyDelete
    Replies
    1. Hi,

      Please try the following expression:

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

      Delete
  5. 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

    ReplyDelete
    Replies
    1. please change the numerator of the percentage calculation as "rangesum(Above(sum(SalesAmount),0,NoOfRows()))"

      Delete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Awesome 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 ?
    ________________________________________________________________
    | | | |
    |___2_____|_____________20_______________________|_____5__________|

    0% 50% 100%




    ReplyDelete
  8. Hi Aydin,
    I 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??

    ReplyDelete
  9. got solution by adding a max() function to aggr() function


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

    ReplyDelete


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

    ReplyDelete