Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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
Can you load your sample file or apps?
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)
)
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
Excellent! It worked! Thank you very much!