Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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))

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
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