Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kennethholden
Contributor

Nested Set Analysis Help

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

1 Solution

Accepted Solutions

Re: Nested Set Analysis Help

Try this

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

7 Replies
jothimala
New Contributor II

Re: Nested Set Analysis Help

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

kennethholden
Contributor

Re: Nested Set Analysis Help

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
Valued Contributor II

Re: Nested Set Analysis Help

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
Honored Contributor

Re: Nested Set Analysis Help

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)

Re: Nested Set Analysis Help

Try this

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

kennethholden
Contributor

Re: Nested Set Analysis Help

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)

Re: Nested Set Analysis Help

Try this

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

Community Browser