Nested Set Analysis Help

[Anonymous]

Aggr(Rank(Sum({<AGENT_LAST_NAME>} UNITS)), {<LEAD_SOURCE-={'DAT'}>} AGENT_LAST_NAME)

THis is an expression designed to provide the agents RANK in new business only, excluding database(existing customers).

the aggr formula works independently, but when I add the second set analysis to tell qlik to exclude the units that are "dat", it gives me no result.

what did I do wrong?

thank you

[jothimala]

Hi

Actually the basic syntax of aggr() is Aggr(Expression, Dimension)

In your script the expression part is correct, the dimension part is not right.

Try putting {<LEAD_SOURCE-={'DAT'}>} AGENT_LAST_NAME within any aggregate function ,because the set expressions can be used only within aggregate function only

Else you exclude LEAD_SOURCE-={'DAT'} in the expression part itself

[Anonymous]

thank you

could actually write the expression as you would do it? I am quick new still and just venturing into this aggr area with nested set analysis. if you write it I will then know for the future.

thank you

[sunilkumarqv]

Hello Kenneth ,

Can you try using below and let us know

Aggr(Rank(Sum({<AGENT_LAST_NAME,LEAD_SOURCE-={'DAT'}>} UNITS)) AGENT_LAST_NAME)

[balabhaskarqlik]

May be this:

Example: Aggr(Rank(Sum(Sales)),Product,SkuCode)

From the example:

Aggr(Rank(Sum({<AGENT_LAST_NAME=>} UNITS)), ({<LEAD_SOURCE-={'DAT'}>}AGENT_LAST_NAME), AGENT_LAST_NAME)

Or

Aggr(Rank(Sum({<AGENT_LAST_NAME=, LEAD_SOURCE=e({$<LEAD_SOURCE = {'DAT'}>}Units)>}Units)) ,AGENT_LAST_NAME)

[sunny_talwar · Accepted solution]

Try this

Aggr(Rank(Sum({<AGENT_LAST_NAME, LEAD_SOURCE-={'DAT'}>} UNITS)), AGENT_LAST_NAME)

[Anonymous]

worked perfectly

tried exact same thing with rank in sales(package ID) and didnt work.

can you advise why?thanks

Aggr(Rank(count(distinct({<AGENT_LAST_NAME,LEAD_SOURCE-={'DAT','DAO','KHA'}>} PACKAGE_ID))), AGENT_LAST_NAME)

[sunny_talwar]

Try this

Aggr(Rank(Count(DISTINCT {<AGENT_LAST_NAME, LEAD_SOURCE -= {'DAT', 'DAO', 'KHA'}>} PACKAGE_ID)), AGENT_LAST_NAME)