topic Re: How to customize subtotal calculation to exclude null rows in QlikView

How to customize subtotal calculation to exclude null rows

JeffQV — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: How to customize subtotal calculation to exclude null rows

parthesh — Wed, 29 May 2019 09:10:02 GMT

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

Regards,
Parthesh.

Re: How to customize subtotal calculation to exclude null rows

Anil_Babu_Samineni — Wed, 29 May 2019 13:14:50 GMT

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

Sum(Aggr(Expression, Dimension))

Re: How to customize subtotal calculation to exclude null rows

JeffQV — Fri, 31 May 2019 16:29:16 GMT

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