Skip to Content
0

Only aggregate for distinct values BEx Query Designer

Jan 24, 2017 at 07:42 AM

85

avatar image

Hi experts,

I have the following requirement.

OppID | Partner | PartnerFunction | Exp.RevenueOpp |

O1 | P1 | PF1 | 1000

O1 | P2 | PF2 | 1000

O1 | P1 | PF3 | 1000

O2 | P1 | PF1 | 2000

O2 | P2 | PF2 | 2000

Exp.RevenueOpp contains the same value per distinct opportunity (OppID). OppID+Partner+PartnerFunction is the primary key.

If I remove the Partner and PartnerFunction, the list looks looks as follows:

OppID | Exp.RevenueOpp |

O1 | 3000

O2 | 4000

It should, however, look like this:

OppID | Exp.RevenueOpp |

O1 | 1000

O2 | 2000

I tried with nested exception aggregation with referenced characteristics = Partner and PartnerFunction but it does not work.

Does a solution on query level exist?

Best regards

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Koen Hesters Jan 24, 2017 at 08:18 AM
1

Hi,

The exception is needed on OppId, the normal aggregate behavior is not applying on OppID, so OppID should be your exc agg refer char.

Grtz

Koen

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Koen,

thank you for your reply. You are right!

I was just able to resolve the issue with the following steps:

1. Create formula on key figure with exception aggregation "first value" on Partner

2. Create formula on the formula of step 1 with exception aggregation "first value" on PartnerFunction

3. Create formula on the formula of step 2 with exception aggregation "summation" on OppId

A little bit complicated, but now the query is just running fine!

0