Pages

▼

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


at

14 comments:

  1. Anonymous27 July, 2012 09:04

    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. Unknown19 March, 2013 01:07

    This comment has been removed by the author.

    ReplyDelete
  3. Unknown19 March, 2013 01:20

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

    ReplyDelete
    Replies
    1. Anonymous21 January, 2014 05:46

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

      Delete
  4. GuillermoAR20 November, 2013 11:59

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

    ReplyDelete
    Replies
    1. I.B. AYDIN21 November, 2013 21:45

      Hi,

      Please try the following expression:

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

      Delete
  5. Motta21 February, 2014 09:17

    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. I.B. AYDIN22 February, 2014 21:08

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

      Delete
  6. I.B. AYDIN22 February, 2014 21:08

    This comment has been removed by the author.

    ReplyDelete
  7. Anonymous16 March, 2015 05:58

    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. Kiran Kumar Cherukupally02 April, 2015 22:20

    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. Kiran Kumar Cherukupally09 April, 2015 01:53

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


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

    ReplyDelete
  10. Kiran Kumar Cherukupally09 April, 2015 01:55



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

    ReplyDelete
  11. Kiran Kumar Cherukupally09 April, 2015 01:55

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

    ReplyDelete