Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
JeffQV
Partner - Contributor II
Partner - Contributor II

How to customize subtotal calculation to exclude null rows

I have the following columns:

Screen Shot 2019-05-28 at 12.49.13 PM.png

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?

 

3 Replies
parthesh
Creator
Creator

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))

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
JeffQV
Partner - Contributor II
Partner - Contributor II
Author

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