topic AGGR function in QlikView

AGGR function

microwin88x — Fri, 14 Mar 2014 12:43:59 GMT

Hello! I have the following question:

I have a Pivot table with:

Where in # Attentions I want to count if # Attentions is > 1 (for the corresponding Category and Account)

(***) I have this calculated dimension and it works:

= IF(AGGR(COUNT (DISTINCT TD_ENTRY_ID), CATEGORY, ACCOUNTID) > 1, ACCOUNTID)

But now I'd like to add the following logic:

IF (CATEGORY = 'OTHERS', PERSONID, ACCOUNTID)

Where I need to use the Person ID when the Category is Others, insted of the Account ID

So I would have:

= IF(AGGR(COUNT (DISTINCT TD_ENTRY_ID), CATEGORY, ACCOUNTID) > 1, ACCOUNTID)

= IF(AGGR(COUNT (DISTINCT TD_ENTRY_ID), CATEGORY, PERSONID) > 1, PERSONID)

How could I do that? Is there any way to use a variable to know if Category is Others or not and put it there?

= IF(AGGR(COUNT (DISTINCT TD_ENTRY_ID), CATEGORY, variable) > 1, variable)

Thank you very much!!!

MK_QSL — Fri, 14 Mar 2014 12:45:31 GMT

Can you load your sample file or apps?

Anonymous — Fri, 14 Mar 2014 13:33:29 GMT

Yes, a sample app would be helpful. For now, try this:

if(CATEGORY = 'OTHERS',

IF(AGGR(COUNT (DISTINCT TD_ENTRY_ID), CATEGORY, PERSONID) > 1, PERSONID)

IF(AGGR(COUNT (DISTINCT TD_ENTRY_ID), CATEGORY, ACCOUNTID) > 1, ACCOUNTID)

)

jonathandienst — Fri, 14 Mar 2014 13:50:41 GMT

I suggest that you set this in script, so during your load of the relevant table:

LOAD

...

IF (CATEGORY = 'OTHERS', PERSONID, ACCOUNTID) As LOOKUPID,

...

And now just use LOOKUPID as the dimension and in your aggr function without conditionals.

HTH

Jonathan

microwin88x — Fri, 14 Mar 2014 14:18:20 GMT

Excellent! It worked! Thank you very much!