Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Can you please upload the QVW file
Regards,
Kaushik Solanki
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
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
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?
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
Hi,
What i can see is there are no null values in year 2009.
Regards,
Kaushik Solanki
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 <
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
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 <
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