topic Aggregating excluding Null in QlikView

Aggregating excluding Null

Mon, 20 Jun 2011 07:30:28 GMT

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

Aggregating excluding Null

kaushiknsolanki — Mon, 20 Jun 2011 07:50:30 GMT

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

Aggregating excluding Null

Mon, 20 Jun 2011 07:55:19 GMT

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

kaushiknsolanki — Mon, 20 Jun 2011 07:57:45 GMT

Hi,

Are you using a calculated dimension..

Can you upload the qvw file.

Regards,

Kaushik Solanki

Aggregating excluding Null

SunilChauhan — Mon, 20 Jun 2011 08:02:36 GMT

hello you can use formula like below:

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

thanks

Sunil

Re: Aggregating excluding Null

Mon, 20 Jun 2011 08:06:21 GMT

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

Aggregating excluding Null

SunilChauhan — Mon, 20 Jun 2011 08:09:27 GMT

count(U.Code)/

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

Re: Aggregating excluding Null

Mon, 20 Jun 2011 08:19:31 GMT

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.

Re: Aggregating excluding Null

SunilChauhan — Mon, 20 Jun 2011 08:27:33 GMT

count(U.Code)/

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

use this code .

this may help u

Aggregating excluding Null

Mon, 20 Jun 2011 08:33:27 GMT

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

Aggregating excluding Null

kaushiknsolanki — Mon, 20 Jun 2011 08:54:58 GMT

Hi,

Can you please upload the QVW file

Regards,

Kaushik Solanki

Re: Aggregating excluding Null

Mon, 20 Jun 2011 11:02:37 GMT

Here is a modified sample of what I am working with. Maybe you might have

better luck.

It is fairly large, so I hope you'll be able to open it without any

problems.

Thanks again for the help.

Ania

Aggregating excluding Null

kaushiknsolanki — Mon, 20 Jun 2011 11:39:23 GMT

Hi,

If you dont want the last null value from the dimension.

You only have to click on supress when value is nul. As i said earlier.

Note :- Do click on the dimension before clicking on Supress when value is null.

Regards,

Kaushik Solanki

Aggregating excluding Null

Mon, 20 Jun 2011 13:40:47 GMT

Thanks for the response. I have been trying to do that,but it does not change the 171435 aggregated value to 169277 (171435 (Total) less 2158 (Null Values) for the year 2009).

Currently for 0-100K the percentage is 43.6% (74810/171435) but it should be 44.1% (74810/169277) if I excluded the null values. This does not happen even when I suppress the Null values having clicked on the calculated dimension.

Question: Does that number change for you when you do it? Could it be a possibility that I may have a problem with my system?

Re: Aggregating excluding Null

SunilChauhan — Tue, 21 Jun 2011 07:59:08 GMT

as per my experience

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

Your need to check weither data is available for 2010 and 2011.

or may not be properly linked

Re: Aggregating excluding Null

kaushiknsolanki — Tue, 21 Jun 2011 09:02:48 GMT

Hi,

What i can see is there are no null values in year 2009.

Regards,

Kaushik Solanki

Re: Aggregating excluding Null

Tue, 21 Jun 2011 09:16:29 GMT

Hi,

Yes, you are right. Ultimately there shouldn't be any null values, but for

some reason it is creating a null band and there are records being placed

into it. Do you think it is calculation error in the bands. I was so careful

when creating those bands as it gets a bit tricky and complicated.

Regards

Ania

On 21 June 2011 11:03, Kaushik.solanki <

Aggregating excluding Null

kaushiknsolanki — Tue, 21 Jun 2011 09:25:38 GMT

Hi,

The null band is created due to the expression you have used.

I will suggest you to create band from the script only.

Regards,

Kaushik Solanki

Re: Aggregating excluding Null

Tue, 21 Jun 2011 09:38:07 GMT

Thanks for all the help.

It's a pity. I was trying to create a field that is dynamic, as I have an

in-line script that loads various rates and those are used in the dimension

to deflate the numbers for 2010 and 2011 (hence why those numbers are not

showing as I didn't load those rates in the sample).

You wouldn't have an alternative suggestion to the way the dimension could

be written to avoid an empty band without going the scripting route?

Thanks again for all your help.

Ania

On 21 June 2011 11:25, Kaushik.solanki <

Aggregating excluding Null

kaushiknsolanki — Tue, 21 Jun 2011 09:56:33 GMT

Hi,

I know nothing about the logic you are applying to create that dimension.

But i think scripting route is the easy and more useful.

Regards,

Kaushik Solanki