Skip to Content

Calculate average for item records

Dear friends,

I need to calculate the average period ZPERIOD (number of days) for purchase order items.

The keys in ODS are: LOGSYS,0DOC_NUM,0DOC_ITEM

The analysis is based on info cube.

I have created a new characteristic, say ZKEY, assigned it to the cube (as line dimension) and fill this characteristic in update rules as concatenated value of LOGSYS,0DOC_NUM and 0DOC_ITEM.

The key figure ZPERIOD has been defined with exception aggregation 'average for all values' for characteristic ZKEY.

The query works fine, but I still have some concerns regarding modelling:

- is there any smartest way to calculate the average based on the item records?

- The line dimension ZKEY will have a lot of entries: number of purchase order * number of items ⚠️ and actually used only for exception aggregation. For the business purpose I still need to include 0DOC_NUM in the cube as another line dimension.

Are there any better ways to calculate average based on item records without creation of the concatenated key?

Thank you !

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Dec 26, 2007 at 11:43 AM

    Better way

    Extractor 2LIS_02_ITM- Purchasing Item

    Field NOPOS map this count of total purchase order items

    This will give you the count of total number of purchase order items. Divide this with any factor you want to get the average

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 08, 2008 at 10:17 AM

    Thank you for the answer.

    I have 0NO_POS in the cube, but I'm not quite sure how this can help me ...

    Here are the more detals on the requirement:

    I need to calculate the average periods (key figure ZPERIOD1, ZPERIOD2, etc...) not over all records in the cube, but only for the records that are relevant for the corresponding period. For example, the records, that are relevant for the calculation of average period ZPERIOD1 have characteristic ZCH1 = 'X', the records that are relevant for ZPERIOD2 have characteristic ZCH2 = 'X' etc...

    The same record can be relevant for different periods.

    To calculate the average periods on the item level I have created a compound characteristic ZKEY, consisting of the concatenated keys of the ODS (where the data came from) and defined all key figures ZPERIOD1... ZPERIODn with exception aggregation 'average for all values' for characteristic ZKEY. In the query I have restricted every ZPERIODn with ZCHn.

    So I'm not sure that 0NO_POS can be used as alternative way

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.