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: 
Not applicable

Aggregating excluding Null

Hi

Is there anyway to do an aggregation function without including the Null values? I have created a band that is creating values that are Null within the Dimensions tab. I have tried suppressing when the values are Null, but this does not work as my Expression which contains the aggregation still shows the sum including the Nulls. I have also noticed that the Label that is given to the Dimension cannot be used in the expression, as I would have liked to have used the set analysis to exclude it.

Any suggestions?

Thanks, Ania

20 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

   You can do one thing.

   In property window, go to Dimensions -> You will see one option named, Supress When Value is NULL.

   Click on this option and check your result.

   Hope this will solve your problem.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

Hi Kaushik

Thanks for the response. I have tried that, and that does not change the aggregation value as it still shows the sum of the numbers including the Nulls. (I did think that strange since I have seen it work before.)

Regards, Ania

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

    Are you using a calculated dimension..

    Can you upload the qvw file.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
SunilChauhan
Champion
Champion

hello you can use formula like below:

aggr(If(isnull(filed1)=0,filed1),Field2)

thanks

Sunil

Sunil Chauhan
Not applicable
Author

Hi Kaushik

The dimension is a calculated one.

Unfortunately I can't upload the file as it is much too large. I can say that I am using prices that are banded into 6 bands. I am then counting the number of variables that fall into each of those bands. This is done over 3 years. The bands are the calculated field as I had to deflate the values over the 3 years to be able to compare like with like. That is where the Null values are being created. The frequency formula is as follows:

count

(U.Code)/aggr(nodistinct count(U.Code),U.Year)

Regards, Ania

SunilChauhan
Champion
Champion

count(U.Code)/

aggr(nodistinct count(if(isnull(U.Code)=0,U.Code)),U.Year)

Sunil Chauhan
Not applicable
Author

Hi

Thanks, I tried that but there are no Nulls in the U.Codes. The Nulls occur in the calculated dimensional field for the prices in the bands.

The Results so far:

Bandcount(U.Code)aggr(nodistinct count(U.Code), U.Year)
0-100189661419653
101-200137980419653
201-30037395419653
301-40013501419653
401-5004756419653
501 +5117419653
-31243419653


I need the 419653 to not include 31243 therefore = 388410.

SunilChauhan
Champion
Champion

count(U.Code)/

aggr(nodistinct count(if(isnull(Band)=0,U.Code)),U.Year)

use this code .

this may help u

Sunil Chauhan
Not applicable
Author

This makes the 3rd column = 0 for all the bands.