Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Aggregating excluding Null

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

Not applicable

Aggregating excluding Null

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

Aggregating excluding Null

Hi,

    Are you using a calculated dimension..

    Can you upload the qvw file.

Regards,

Kaushik Solanki

chauhans85
Esteemed Contributor

Aggregating excluding Null

hello you can use formula like below:

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

thanks

Sunil

Not applicable

Re: Aggregating excluding Null

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

chauhans85
Esteemed Contributor

Aggregating excluding Null

count(U.Code)/

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

Not applicable

Re: Aggregating excluding Null

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.

chauhans85
Esteemed Contributor

Re: Aggregating excluding Null

count(U.Code)/

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

use this code .

this may help u

Not applicable

Aggregating excluding Null

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

Community Browser