Calculate average for item records

[former_member636372]

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 !

[former_member636372]

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

[Former Member]

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