Analyst calc options

[qweejy]

Hello,

         I'm relatively new to Cognos Planning Analyst and have inherited a problem.  We have a field which we cannot seem to get to recalculate correctly at summary levels.

         We are using CP to plan inventory and have a product hierarchy like:

Apparel
    Dresses
    Bottoms
    Jackets
    Sweaters
    Tops
    Outerwear

Our d-cube is made up of a d-list of metrics, a d-list of the product hierarchy (described above), and time (weeks, months, quarters, seasons, and total year).  The metric we have trouble summarizing is our Weeks on Hand.  The weeks on hand is calculated by dividing our Beginning of Period On Hand ($) by the Cost of Sales for the period ($/week).  This works fine at the detail level of department and week (e.g., Dresses for week 1), but does not roll up to the Apparel category because the weeks for each department are not summable (likewise for the week to month level) and weighted averages won't work in this situation.  We've gotten around this for the category/department hierarchy by adding a pseudo category of review formulas, so our d-list looks like:

Review Formulas
Apparel
    Dresses
    Bottoms
    Jackets...

We then have a d-link which will move the summarized components of the calculation to the values in the Review Formula level.  This allows the components to be summarized before the calculation is performed.  This is not an ideal situation because our planners do not get real time feedback in their what if analyses.  They have to run a d-link and toggle between d-list items to see the results of their changes.  The problem of summarizing the time dimension is solved in another way.  We created three new week counters weeks(week), weeks(month), weeks(quarter) and also created a month and quarter version of our beginning of period inventory and cost of sales values.  We created d-links to move the appropriate summary level fields into the detail level fields (i.e., the month's beginning of period inventory value and the sum of costs for all sales for the month are placed in the weeks of that month).  The calculation then yields a "period" on hand...in order to get the weeks on hand we needed to multiply by the weeks in the period to get the WOH.   The result is three different WOH values WOH (week), WOH (month), and WOH (quarter) which are accurate at the detail level and have a calc option of force to zero.  It can be confusing for the planners and we are dealing with more d-links that prevent real-time what ifs.

My question is "Is there a better way to do this?"

                      Thanks in advance,
                      Steve

[ykud]

Hi, qweejy

Can you add a sample of what you're trying to achieve?
A spreadsheet or a table would be great to understand the required calculations, especially to understand why 'weighted averages won't work in this situation'.