AGGR function

[microwin88x]

Hello! I have the following question:

I have a Pivot table with:

• DIMENSION Category
• DIMENSION Account (***)
• DIMENSION Office
• EXPRESSION # Attentions

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]

Can you load your sample file or apps?

[Anonymous]

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 · Accepted solution]

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]

Excellent! It worked! Thank you very much!