7 Replies Latest reply on Mar 11, 2018 5:58 PM by Patrick Myles

# LOD of Top N

Hey all - (this seems like it should be easy.. but) - I am trying to compare a single 'agent' and compare it to the top N agents all in the same view using an LOD.  Not sure what i'm doing wrong here.  the Top N column should show the sum of top N agents by subscriptions.

the reason i want to do this is because the next step is that i will create a growth ratio at each date where a single agent will be on the same view as the ratio of the top N.

see below and attached.  hope someone might help out here.

• ###### 1. Re: LOD of Top N

This formula should work

if [Set 1] then [Subscriptions] else 0 end

• ###### 2. Re: LOD of Top N

hey, thanks for this, however not sure it's what i want.  A couple points: if you add up the values and compare manually to the top 10 at each date, the values are slightly different.  Plus, how do you then put a single agent in the view to compare with the top 10 aggregate?

• ###### 3. Re: LOD of Top N

Not Sure, If You Need this?

• ###### 4. Re: LOD of Top N

No that's not it unfortunately. It needs to show the top N

• ###### 5. Re: LOD of Top N

hi again.  after thinking more i realise you might be half there.  however, you'll notice that when you bring a specific agent into the view (to compare their values against the sum of the top 10 ), the top 10 values  change... I have even tried the below using an exclude on the agent ID .. but as soon as i bring the agent into a filter, the top N values change.  Any ideas?

• ###### 6. Re: LOD of Top N

Correct me if my understanding is wrong.  Basically, you want to find the top 10 agents by their subscriptions per month.  If so, you need to fix the calculation to the date.  Try this:

• ###### 7. Re: LOD of Top N

Hi Christoper.  Yes, you got it!  I can't believe it's that simple.  This has helped me enormously as would you believe i spent almost my entire weekend on this.   You saved me!!

I have used the below which as you see simplifies the date part too.

{ FIXED [Date]:

SUM(IF [Set 1] THEN [Subscriptions] END )}

Thank you once again !!!!

Patrick