cancel
Showing results for 
Search instead for 
Did you mean: 

Restricted Key figure in Qery

Former Member
0 Kudos

I have a critical problem - many points for a solution :). In my cube (Purchasing data - 0PUR_C01) I have several invoices that correspond to a material. Each invoice has an invoice date and an invoice amount;

Cube contents:

Material A - 2004-01-14 - 1000 USD

Material A - 2004-03-05 - 1500 USD

Material A - 2004-12-20 - 1700 USD

***

Material B - 2004-03-15 - 100 USD

Material B - 2004-04-17 - 200 USD

Material B - 2004-11-30 - 300 USD

Now, in the report I need to define a restricted (or calculated, or anything) key figure containing the amount from the latest invoice for each material. In the examples above the result would be:

Material A - 1700 USD

Material B - 300 USD

The restricted key figure is later to be used as a base in price development calculations.

How can I solve this, if it’s not possible to solve in the report I’m ready to restructure the flow.

Accepted Solutions (1)

Accepted Solutions (1)

GFV
Active Contributor
0 Kudos

Did you tried to define an calculated KF (=invoice) with Exception Aggregation LAST value on the Time Characteristic?

The CKF must be "global", so deifned at InfoCube level to use exception aggregation

Hope it helps.

GFV

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Daniel,

You can achieve it with the Aggregation settings of the Key Figure as follows:

Aggregation: SUM

Exception aggregation: Last value

Agg. referen. char 0CALDAY or whatever temporary characteristic you use in the cube.

You must consider that this setting will affect the KPI aggregation procedure and probably this won't be the expected result in the other queries so you better create a copy of the KPI with this settings and map it with initial KPI in the Update rules of the cube.

Hope it helps,

Andreu

Former Member
0 Kudos

Thanks guys it seems to work,

The problems You mention - couldn't they be solved by creating a calculated key figure and do the settings on that?

Best Regards

/D