topic Bottom 5 in QlikView

Bottom 5

varunreddy — Thu, 01 Dec 2016 21:49:05 GMT

Hi,

I am trying to show top 5 Assessment Names using the rank function in the expression. It is working fine.

Can any one guide me how to show least 5 values using the below expression:

1-(aggr(sum({<Date,

[Assessment Name] = {"=rank(1-(aggr(sum({<Date = {'$(vCurMonthYear)'}>}[Total Number of Failing Records]),Date, [Assessment Name])
/
aggr(sum({<Date = {'$(vCurMonthYear)'}>}[Total Number of Records]),Date,[Assessment Name])))<=5"}

>}[Total Number of Failing Records]),Date,[Assessment Name])
/
aggr(sum({<Date>}[Total Number of Records]),Date,[Assessment Name]))

Thanks In advance!

Cheers,

Varun Reddy

Re: Bottom 5

sinanozdemir — Thu, 01 Dec 2016 21:57:50 GMT

Hi,

Have you tried putting "-" in front of Sum() functions?

"=rank(1-(aggr(-sum({<Date = {'$(vCurMonthYear)'}>}[Total Number of Failing Records]),Date, [Assessment Name])
/
aggr(-sum({<Date = {'$(vCurMonthYear)'}>}[Total Number of Records]),Date,[Assessment Name])))<=5"}

>}[Total Number of Failing Records]),Date,[Assessment Name])

Thanks

Re: Bottom 5

varunreddy — Thu, 01 Dec 2016 21:59:11 GMT

Hi Sizan,

I tried this. But it is displaying all Assessment Names

Re: Bottom 5

sunny_talwar — Thu, 01 Dec 2016 22:01:30 GMT

It might be possible that all bottom 5's are 0?

Re: Bottom 5

varunreddy — Thu, 01 Dec 2016 22:02:44 GMT

Sunny,

I have unchecked Suppress Zeros

Re: Bottom 5

varunreddy — Thu, 01 Dec 2016 22:28:28 GMT

I am using line graph. When I hover over 0, I am seeing top 5, but in legend I am seeing all the Assessment Names.