Advanced Aggregation

[arsenal1983]

Hello,

I have the data as attached.

I need to count:

1. Number of Partners (PARTNER_NAME)

     a) only from PARTNER_GROUP = GR_01

     b) only from LIMIT_CATEGORY = LIMIT

     c) which in one QTR had at least 5 INVOICES (DOC_ID) overdue over 1 days (OVERDUE)

I need to know just one number (with no dimmennsions)

Luke

[#2]

Do you need to apply all three filters in one expression or separately ?

[#3]

Try below:

Sum( {<PARTNER_GROEUP={"GR_01"},LIMIT_CATEGORY={"LIMIT"},OVERDUE={">=1"}>}

          IF(AGGR(Count({<PARTNER_GROEUP={"GR_01"},LIMIT_CATEGORY={"LIMIT"},OVERDUE={">=1"}>}DOC_ID) , PARTNER_NAME , PERIOD) >=5 , PARTNER_NAME)

       )

[effinty2112]

Try:

Count(DISTINCT Aggr(if(Min(Aggr(count({$<LIMIT_CATEGORY = {'LIMIT'}, PARTNER_GROUP = {'GR_1'}>}DOC_ID), PARTNER_NAME,PERIOD))>=5,PARTNER_NAME),PARTNER_NAME))

[arsenal1983 · Accepted solution]

Very close,

The correct answer is:

=count(DISTINCT

{$<PARTNER_GROUP = {'GR_1'}, LIMIT_CATEGORY = {'LIMIT'}, OVERDUE = {">$(vOVERDUE)"}>}

If(Aggr(count({$<PARTNER_GROUP = {'GR_1'}, LIMIT_CATEGORY = {'LIMIT'}, OVERDUE = {">1"}>} DOC_ID), PARTNER_NAME, PERIOD) >=5,  PARTNER_NAME))

Thanks for your help!