Average of Totals

[#1]

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

[#2]

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.

[#3]

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

[#4]

You can use SUM({<FEE_EARNER.department_code={'7','8'}, FEE_EARNER.department_description = >}WIP_BILLED_VALUE)/9 for **, since both the expressions are calculated at period_description level.

Hope it helps,

Kiran.