Only aggregate for distinct values BEx Query Designer

[former_member331260]

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

[kohesco · Accepted solution]

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

[former_member331260 · Accepted solution]

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!