RANK() with set analysis

anagharao — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: RANK() with set analysis

tresB — Tue, 12 Jul 2016 04:33:40 GMT

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

Re: RANK() with set analysis

Tue, 12 Jul 2016 07:01:00 GMT

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

topic Re: RANK() with set analysis in QlikView

RANK() with set analysis

Re: RANK() with set analysis

Re: RANK() with set analysis