Sum of Averages Problem

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

  • 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
  • Replied 3 August 2017, 3:59 pm EST

    Mark,

    From the description provided by you it looks like the two fields which you have, already show the total values for volume and loading amount. Since you are looking to get Cost per BCM, you can simply add a calculated measure which divides one field from another and it should work. However I could see from your post that you tried it and it didn't work. Could you please provide me additional information about the expression which you used? As stated at the beginning of this post, please clarify whether the two fields already show the total values for volume and loading amount or you have to calculate the total as well? Any additional information provided to narrow down the issue would be appreciated.

    Regards,
    Sankalp
  • Replied 3 August 2017, 3:59 pm EST

    Hi Sankalp.

    Thankyou for your reply.

    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
  • 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
  • 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


  • 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


    Any ideas please.

    Kind regards
    Mark Bailey
    Ulaanbaatar, Mongolia
  • 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





  • 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
  • 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
  • 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 expression for the field "$/BCM" has not been set correctly. The 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
Need extra support?

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

Learn More

Forum Channels