Aggregating excluding Null

[#1]

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

[kaushiknsolanki]

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

[#3]

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]

Hi,

    Are you using a calculated dimension..

    Can you upload the qvw file.

Regards,

Kaushik Solanki

[SunilChauhan]

hello you can use formula like below:

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

thanks

Sunil

[#6]

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]

count(U.Code)/

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

[#8]

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:

Band	count(U.Code)	aggr(nodistinct count(U.Code), U.Year)
0-100	189661	419653
101-200	137980	419653
201-300	37395	419653
301-400	13501	419653
401-500	4756	419653
501 +	5117	419653
-	31243	419653

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

[SunilChauhan]

count(U.Code)/

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

use this code .

this may help u

[#10]

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