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,

    Can you please 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!
Not applicable
Author

Hi

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

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

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?

SunilChauhan
Champion
Champion

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

Sunil Chauhan
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

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

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,

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 <

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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  

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

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 <

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

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