Sum of Averages Problem

Posted by: c1guest_1342189 on 3 August 2017, 3:59 pm EST

• Post Options:

Posted 3 August 2017, 3:59 pm EST

Hi,

I am evaluating the Active Analysis product and have an issue that I am hoping someone might be able to answer.

I have two raw measures in my data.
Volume (BCM - Bank Cubic Meter) and Cost (\$)
I have created a measure called which represents Cost per BCM and set it to Average.
I have tried Aggregate, Distinct Sum and Calculated but none of those show any values

At the record level the Record cost / Record Volume = \$/BCM

On the Active Analysis below, notice the AVG Loading \$/BCM = 0.68. This is indeed the average but
that is not the answer I need. The correct answer is  11,150.77 / 19,785 = 0.56

I am sure there is a simple way of doing the and clearly setting the field to Average is not the answer.

Any assistance would be appreciated

Cheers,
Mark Bailey
Ulaanbaater, Mongolia
• Post Options:

Replied 3 August 2017, 3:59 pm EST

Mark,

Regards,
Sankalp
• Post Options:

Replied 3 August 2017, 3:59 pm EST

Hi Sankalp.

Here is a screenshot of the Calculated Field called test

Here is a screenshot of the result in the control....

I have attached a spreadsheet of the underlying data.

Notice the calc at the bottom.  The correct answer should be 0.56 rather than 0.62

Hope this helps.

When I look at all of the data....some rows produce a zero result also even though there are values in Volume and Loading_Amount.

Kind Regards,
Mark Bailey
Ulaanbaatar, Mongolia.

2011/10/GrapeTest.xls
• Post Options:

Replied 3 August 2017, 3:59 pm EST

Mark,

Thank you for providing such detailed information. I could notice what exactly are you referring to. I would like you to download the latest build 2.1.706.0 from this link as I was able to get the correct results with it. You should simply select the composite function from the list of available aggregation functions, write the expression in the same manner as you have done now and it will give you the correct results. I have attached a screenshot of the output which I got at my end. I have simply added two fields having the same values which you have, and just displayed the result by placing the calculated measure on the data shelf.

Regards,
Sankalp

• Post Options:

Replied 3 August 2017, 3:59 pm EST

Mark,

I am not sure why you are not able to install ActiveAnalysis on your machine; however there are couple of things which I would like you to try. Since you have already uninstalled the previous build, the first thing which you may want to do it to try repairing .NET framework 4.0. If this does not help, then perform the following steps:

1) Open Windows Services (Start > Run > "services.msc")
2) Find the Service "Windows Modules Installer" or "Windows Installer"
3) Make sure it is not disabled. If it is Right click -> Properties -> Enable.
4) Make sure the service is started.

If the problems persists then please provide me more information about your machine configuration. What is the OS and whether its 32 bit or 64 bit? What version of Visual Studio is installed on your machine? Any additional information provided to narrow down the issue would be appreciated.

Regards,
Sankalp
• Post Options:

Replied 3 August 2017, 3:59 pm EST

Hi Sankalps

OK. Managed to sort out the installation problem.

Having done what you explained I still get an incorrect result....   0.62 rather than 0.56!

It seems to be no different to the previous version.  The version now installed is 2.1.706.0

Kind Regards,
Mark Bailey
Ulaanbaatar, Mongolia

• Post Options:

Replied 3 August 2017, 3:59 pm EST

Thanx SankalpS

that sounds good.

However, I have downloaded the update and tried to install but get the following error.

So far...
I have uninstalled the original version and tried installing new version....no go.
I have tried installing new very as Administrator....no go
Tried deleting every reference to Grape from registry....no go

Kind regards
Mark Bailey
Ulaanbaatar, Mongolia
• Post Options:

Replied 3 August 2017, 3:59 pm EST

Mark,

Unfortunately, I was not able to reproduce the issue which you have reported. In my tests. I created two fields having the same value as you have in the screenshot. Next, I added a calculated field, which performed a division of the values and the final value which I got was ".56 ". It may be possible that I might be missing something therefore could you please send me the backup of your database which I can use to check this issue further. You can send the database and the schema file at our support E-mail address powersupport@grapecity.com.

Regards,
Sankalp
• Post Options:

Replied 3 August 2017, 3:59 pm EST

Just as an additional information, I would like to add that I received the database from Mark and was able to reproduce this issue. A new Case 156781 has been opened for the development team to address this issue.

Regards,
Sankalp
• Post Options:

Replied 3 August 2017, 3:59 pm EST

Mark,

I would like to tell you that the reason why you are not getting the correct results is because the <span id="BugEvents">expression for the field "\$/BCM" has not been set correctly. </span>T<span id="BugEvents">he expression of the field "\$/BCM" should be
"=SUM(Fields!AMOUNT.Value)/SUM(Fields!VOLUME.Value)" with its aggregate
function set to "Composite" or "Calculated". If we just use the
expression of the field without the aggregate function expression before
it and set its aggregate function to "Calculated", it will just show
the first value. Therefore Case 156781 has been resolved.

Regards,
Sankalp
</span>
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

• ComponentOne

Forums for all current editions of the ComponentOne .NET UI control product line, including ComponentOne Studio and ComponentOne Studio for Xamarin.

• ActiveReports

Forums for all versions of ActiveReports and ActiveReports Server