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

    Average of Totals

      Hi
      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',

      (
      Sum(COSTS_DELIVERED_BY_BILL)/
      (
      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.
      Jon
        • Average of Totals

          Can you try the following as your expression:

          Sum(COSTS_DELIVERED_BY_BILL)/

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

          +Sum(WIP_BILLED_VALUE))

           

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

           

          Hope this helps,

          Kiran.

          • 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.

             

            Jon