3 Replies Latest reply: Oct 25, 2011 1:53 PM by Kiran Rokkam RSS

    Average of Totals

      I have a problem with a Pivot Table, I need to work out the average Write Off % over 12 months i.e. sum(% write off)\12.  I have attached an Excel Sheet which shows some sample data (Sorry I can't upload the QVW due to the nature of the data).  In the example you can see that averaging the Write off % give 80% where as using the same caluation on the total values gives 85%, I need the 80% equivalent.  The Department and Periods are dimensions in a pivot table, Total Billed etc are the expressions.
      If (Dimensionality()=1, 'a',

      Aggr(NODISTINCT SUM({<FEE_EARNER.department_code={'7','8'}, FEE_EARNER.department_description = >}WIP_BILLED_VALUE)/9, period_description)+
      Sum(WIP_BILLED_VALUE)))  * 100)
      I know I need to use Dimensionality and have looked at the Avg function but can't get my head around it.
      Any help would be really appreciated.
        • Average of Totals

          Can you try the following as your expression:


          (SUM(TOTAL <period_description> {<FEE_EARNER.department_code={'7','8'},FEE_EARNER.department_description=>} WIP_BILLED_VALUE)/9



          Dimensionality in QlikView is obtained by TOTAL <Dim1,Dim2> etc.. analogues to group by in SQL.


          Hope this helps,


          • Re: Average of Totals

            Hi Kiran


            Thanks for your reply but it hasn't given me the figures I need. I was looking through the community and came across another post which you answered which is exactly what I need.


            http://community.qlik.com/message/155307#155307    The answer was given as:


            num(avg(aggr(count(distinct sales) / count(distinct presentations)),YearMonth)*100,'0.00%')


            This is exactly what I need to do but my problem is that my original expression includes an Aggr Function already so I'm not sure how to include it in the avg expression. I have written out what I think the expression should be but at the point marked with ** in the avg expression, I need to insert the following formula:


            Aggr(NODISTINCT SUM({<FEE_EARNER.department_code={'7','8'}, FEE_EARNER.department_description = >}WIP_BILLED_VALUE)/9, period_description)


            avg(aggr(Sum(COSTS_DELIVERED_BY_BILL) / ( ** + Sum(WIP_BILLED_VALUE))), period_description)*100


            Any idea how I can put an Aggr inside the avg expression?


            Sorry if that isn't clear I couldn't think of a better way to explain it.