Skip to Content

Only aggregate for distinct values BEx Query Designer

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Jan 24, 2017 at 08:18 AM

    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

    Add comment
    10|10000 characters needed 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!