9 Replies Latest reply: Apr 29, 2009 8:54 PM by Jay Jakosky RSS

    Problem with Advanced Aggregation in Chart (Pivot and Straight Table)

    jdo__

      Dear Community:

      For the past few days I have been playing around with getting a P/L statement to work, but so far I have failed to accomplish the objective and therefore I would like to ask the community for some input.

      I am working for a shipping company and our voyages generally last from a few days to several weeks or is some cases several years. In a Year to Date Profit-Loss statement we would like to include only the percentage of a particular costs that can be allocated based on a key of the time of a specific voyage within as a percentage of the total voyage duration. To do this I have used the following formula:

      SUM(
      VOS_Actual_AmountUSD
      * AGGR((SUM([Calendar - On Hire] * [Calendar - Year 2 Date]) / [Voyage Net Duration]), _KEY_DBFixture)
      )

      This and the example datamodel with simplified data can be found in the enclosed QVW file. The object of interest is the orange object with the name P/L.An explaination for the various columns:

      • Account label speaks for itself.
      • Voy: This is the voyage number of the demonstration vessel.
      • Recard #: Just a line number for each record in the ledger.
      • Total Amount: SUM of 100% of the Amount
      • % in period: the percentage of the voyage within the Year-to-date period.
      • "Amount allocated to period": The formula quoted above should be in this field but does not work correctly. For example note: note that the 8th line under voy 3 is not populated, while the "Total Amount" column is. The formula needs to work in such a way that the pivot table can be callapsed and still shows the accurate amount.
      • The last column shows the correct value of the column (only works at the lowest detail level).

      Does anybody have an idea how to fix the formula? Please do not hesitate to ask clarifying questions if needed.

      Thanks.
      JD