Averages of Averages

As we all know, averages of averages are generally wrong , what most reports need is weighted averages when subtotaling in groups.

I have implemented gross sums in my DB views and specific counts (to do division with). but I'm having problems implementing the calculations in the aggregation tool with its strange combo of syntaxes.. can I get a pointer to some resources for this? I.E. testing for numbers, and aggregating fields and doing math and string operations within the aggregation formula box??

Thanks!

Find more posts tagged with

Comments

Dartdog

Getting a birt failure that it cannot convert NaN to decimal data type when running a group total on a column. Can I use code to make a blank db field =0 or use code to have the sum function ignore the Blank/ null fields??

I am trying to do multiple group levels of averages like average for new vs used, then by subdivision then by area. So I need to accumulate the gross sum of the prices then divide by the group total of the summed number of homes.

So far I've been beating my head against the wall/ monitor for a few days with no luck.

Dartdog

These both fail, Does not like my syntax and cannot convert NaN error messages:
I just need some basic syntax guide here!

If(dataSetRow["NSTATUS"]="S",dataSetRow["SUM_SOLD_PR"])
What I really want is
If(dataSetRow["NSTATUS"]="S",dataSetRow["SUM_SOLD_PR"]/dataSetRow["SOLD_COUNT"])

Next will be doing similar formulas in the three or 4 group totals

Help appreciated!!

If I was going to put the else in the if it would be 0 to place a number in the field to prevent future numeric errors..

mwilliams

Hi Dartdog, 
 
You could create a new column in your dataSet that repeats the column you're working with, only replacing "null" values with '0'. You'd just have to do something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if (row["Column_Name"] == null){
0;
}
else{
row["Column_Name"];
}
</pre>
 
Then you could put this column in your table rather than the one with null values. If I'm not understanding your problem, let me know.

Dartdog

The solution solved a part but I'm still having issues with syntax and what I can do where. The field result that I create using your method still must be "divided" by a relevant count for an average. Dividing by 0 yields NaN. so I tried the following in the function field but the compiler does not take the 1st { after the If, says it wants ; which make no sense to me. Is there a reference somewhere??? I hate asking dumb stuff in the forums!

If (dataSetRow["NSTATUS"] === "S") {
dataSetRow["Sold_pr"]/dataSetRow["SOLD_COUNT"];
}
else{
0;
}
After this I have to do similar calculations in the group totals, 3 levels... Is there a good reference point for that?

Dartdog

Ok I decided to try this in the main column To test for not 0 as sold price and only do math if it was not zero, I get no compile errors and no run time errors but the field shows on the report as blank... I'm pulling what little hair I have out!
If(dataSetRow["Sold_pr"]!==0)
{
dataSetRow["Sold_pr"]/dataSetRow["SOLD_COUNT"];
}

Also tried to use the "filter function for aggregation using one field to define which rows to aggregate, again, no luck, just blanks...

Dartdog

Finally figured out syntax in "filter" field for aggregation
Like Javascript In the FILTER BY box

dataSetRow["NSTATUS"]==='S'

For the record
dataSetRow["NSTATUS"]="S" does not work!!!

Next I found that I must use aggregated group bindings to "divide by" by picking those names out of the column binding list.

For the time being I have one set of group averages working, still have ugly NaN on my reports but maybe tomorrow something will come to me. Or someone will point out some good examples...or references!!

mwilliams

Dartdog,

In the columns where you're doing the division that is returning NaN, you should be able to do a check on the denominator for values of '0' and put what you want in there if it is. This would get rid of the NaN.

Dartdog

Thank you for your patien reply, but the docs don't seem to me to be too clear can you please post a sample, I'm beginning to figure out the syntax. (but don't have it yet!)

mwilliams

Dartdog,

Is it possible for you to attach your report design?

Dartdog

Here it is, The field I need to test in the sold price Last column, I have the sub total working now, any pointers to documentation? I don't mind reading the fine manuals!!! There seems to be a lack of examples as to what and how to put "stuff" in the formula boxes, just a vague You can use JS, Birt or Java, but not much as to how?

mwilliams

So, your subtotal is working, but you're still getting NaN values in the last column? Is that the current situation?

Dartdog

Thanks for looking!

mwilliams

I can't obviously run the report because I can't connect to your database, but if the problem is with the last column having "NaN" as some of the values, you should be able to fix it with the following:

For your data element "Sold_pr" in the last column, change the expression from

dataSetRow["Sold_pr"]/dataSetRow["SOLD_COUNT"]

-to-

if (dataSetRow["SOLD_COUNT"] == 0){
0;
}
else{
dataSetRow["Sold_pr"]/dataSetRow["SOLD_COUNT"];
}

Dartdog

Seems a few flavors, a suggestion please?
Thank you so much.

mwilliams

Dartdog, 
 
Since we use standard javascript for BIRT, we don't have any real detailed syntax documentation. For most any issue you have with javascript, you can use google to find the correct syntax to use. We also have many reports designs in the <a class='bbc_url' href='http://www.birt-exchange.com/devshare/'>devShare</a> that you can search to see if what you're tying to do has already been done and posted. Many of the examples in there use scripting, so you could download some of those to use as references for how scripting is used within BIRT. You can also go to the <a class='bbc_url' href='http://www.birt-exchange.com/modules/documentation/birt-report-designers.php'>documentation page</a> and find some help on the APIs and the BIRT designer. 
 
Hope this helps.

Dartdog

I'll keep bashing though this with your help!! Reading all I can find on line.
Will try to post what I learn to help others as maybe this thread will.

mwilliams

Sounds good.