Combing IF-statement with Aggr()

[motzfeldt01]

Hi,

I'm using the code below to identify the top five performers and now I want get the average of those top five performers.

If(Rank(TOTAL SUM({$<

[CompanyName]=,

[XBRL Reporting year]={$(vXBRLThisYear)},

industry={$(vChosenIndustry)}>}

[Årets resultat]))<=5,

SUM({$<

[CompanyName]=,

[XBRL Reporting year]={$(vXBRLThisYear)},

industry={$(vChosenIndustry)}>}

[GrossResult]))

I have tried an aggr() function, but have not managed to succeed.

Does anyone have idea how to get the average value of top 5?

[sunny_talwar · Accepted solution]

This isn't working?

Avg({$<[CompanyName], [XBRL Reporting year] = {$(vXBRLThisYear)}, industry={$(vChosenIndustry)}>} Aggr(

If(Rank(TOTAL SUM({$<[CompanyName], [XBRL Reporting year] = {$(vXBRLThisYear)}, industry = {$(vChosenIndustry)}>} [Årets resultat])) <= 5, Sum({$<[CompanyName], [XBRL Reporting year] = {$(vXBRLThisYear)}, industry = {$(vChosenIndustry)}>} [GrossResult]))

, Performer))

[motzfeldt01]

Unfortunately not. No value is shown. But I don't understand where 'Performer' comes from?

[sunny_talwar]

It came from your description of the problem

I guess Performer is not a field name, but is rather called something else... replace Performer with the field which shows performers

[motzfeldt01]

It worked!! Thank you very much and have a nice day 🙂