How to customize subtotal calculation to exclude null rows

[JeffQV]

I have the following columns:

Avg Prod Qty = avg([productionQty])
Avg Expected Qty = avg([ExpectedQty]) // Note: ExpectedQty is actually a reference to another calculated column
Perf. = [Avg Prod Qty] / [Avg Expected Qty]

The problem is the missing values in the 4th row creating a misleading subtotal in the Perf. column

How can I change the subtotal calculation for the [Perf.] column to exclude rows with null values?

[parthesh]

Hello,
i think you have to avoid null, you can replace null with 0 using IsNull().

Regards,
Parthesh.

[Anil_Babu_Samineni]

You can select option as "Sum" in Qlikview. Else, Is there any dimension in your table? If so, use like

Sum(Aggr(Expression, Dimension))

[JeffQV]

I'm using a Pivot Table so I can't control the "Total Mode" for the expressions.

My [Perf.] column is the expression [Avg Prod Qty]/[Avg Expected Qty]

So I need to change the expression to something like: IF RowNo()=0 then sum(aggr(..)) 

But I'm not sure how to use aggr or rangeavg in this case because the column is a calculated expression

Thanks