RANK() with set analysis

[anagharao]

Hi All,

I cant seem to get this right. I have a list of records with set analysis to filter only the approved records, or to show amount for a range of date.

SUM({<[Amount(UN)]={'Approved'}>}AMOUNT)

SUM({<FiscalYear=,MonthShortName=,FiscalMonthKey={"$(='>=' & $(vSelYrMthStart) & '<=' & $(vSelYrMthEnd))"},[Amount(UN)]={'Approved'}>}AMOUNT)

I want to rank this and show only top 20. I always seem to random records for the below. Please help.

SUM({<[Amount(UN)]={'Approved'},[EMPLOYEE NAME]={"=Rank(Sum({<[Amount(UN)]={'Approved'}>}[AMOUNT]))<21"}>}AMOUNT)

SUM({<FiscalYear=,MonthShortName=,FiscalMonthKey={"$(='>=' & $(vSelYrMthStart) & '<=' & $(vSelYrMthEnd))"},[Amount(UN)]={'Approved'},[EMPLOYEE NAME]={"=Rank(Sum({<FiscalYear=,MonthShortName=,FiscalMonthKey={""$(='>=' & $(vSelYrMthStart) & '<=' & $(vSelYrMthEnd))""},[Amount(UN)]={'Approved'}>}[AMOUNT]))<21"}>}AMOUNT)

Regards,

Anagha

[tresesco]

Could you explain a bit more about your requirement with a sample data in a qvw?

[#3 · Accepted solution]

Try something like:

Aggr(Rank(SUM({<[Amount(UN)]={'Approved'},[EMPLOYEE NAME]={"=Rank(Sum({<[Amount(UN)]={'Approved'}>}AMOUNT)

This is untested and might contain a few syntax errors. The inclusion of AGGR should should your problem.

Regards,

Pravesh