topic Advanced Aggregation in QlikView

Advanced Aggregation

arsenal1983 — Tue, 08 Dec 2015 15:28:42 GMT

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

Re: Advanced Aggregation

Tue, 08 Dec 2015 18:27:17 GMT

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

Re: Advanced Aggregation

Tue, 08 Dec 2015 18:35:24 GMT

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)

)

Re: Advanced Aggregation

effinty2112 — Tue, 08 Dec 2015 19:18:22 GMT

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))

Re: Advanced Aggregation

arsenal1983 — Wed, 09 Dec 2015 08:58:48 GMT

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!