topic Re: Nulls in rank in QlikView

Nulls in rank

Thu, 18 May 2017 06:15:10 GMT

while Using the rank function in a straight table. I had taken 3 fields productID, Product Name, and in expression count(Product ID) as Total Products and rank column also in expressions. but i was reloaded successful, and the result is same excatly what i expected , so one more column i added Month the result is showing that some fields are null. the data and the application is attached below

Re: Nulls in rank

qlikview979 — Thu, 18 May 2017 06:22:03 GMT

Hi,

Here it is showing for only April month,

can you please share the sample app.

Re: Nulls in rank

Thu, 18 May 2017 06:23:23 GMT

okay definitely

Re: Nulls in rank

Kushal_Chawda — Thu, 18 May 2017 07:34:36 GMT

try this expression

rank(total count(ProductID)/Count(TOTAL ProductID),0,1)

Re: Nulls in rank

Thu, 18 May 2017 07:47:16 GMT

yeah its working but if we click on anything it is not showing the rank in serial. can you please help me

Re: Nulls in rank

Kushal_Chawda — Thu, 18 May 2017 07:58:41 GMT

So if you select any product or month, then % & Rank which is calculated should not change?

Re: Nulls in rank

Thu, 18 May 2017 08:03:59 GMT

suppose if we want to check the top 10 by rank wise if we click on anything % should not be changed and the rank should be change by top 10 wise . Then what i have to do? can you please say

Re: Nulls in rank

Kushal_Chawda — Thu, 18 May 2017 08:53:44 GMT

I didn't get this. Can you give one example?

Re: Nulls in rank

vinieme12 — Thu, 18 May 2017 10:05:09 GMT

Try below

=AGGR(rank(total count({1}ProductID)/Count({1}TOTAL ProductID),0,1),ProductID)

Re: Nulls in rank

Thu, 18 May 2017 10:09:37 GMT

suppose i want to check top 10 ranks in a year 2008.

Re: Nulls in rank

Thu, 18 May 2017 10:11:16 GMT

sorry it is not working

Re: Nulls in rank

vinieme12 — Thu, 18 May 2017 10:11:50 GMT

there is plenty of content available for similar queries

https://community.qlik.com/search.jspa?q=top+10

Re: Nulls in rank

Thu, 18 May 2017 10:14:52 GMT

okay i'll check the above link thanks for the information

Re: Nulls in rank

vinieme12 — Thu, 18 May 2017 10:15:04 GMT

=if( AGGR( rank(total count(ProductID)/Count(TOTAL ProductID),0,1), ProductID)<=10, sum(sales))

Re: Nulls in rank

Thu, 18 May 2017 10:17:14 GMT

it is showing somw values as zeros and remaining are nulls

Re: Nulls in rank

vinieme12 — Thu, 18 May 2017 10:22:31 GMT

don't know where you are trying this, can you post a screenshot?

Re: Nulls in rank

Kushal_Chawda — Thu, 18 May 2017 10:23:53 GMT

% expression

if(rank(total count({<ProductID,month,ProductName>}ProductID)/Count(TOTAL {<ProductID,month,ProductName>}ProductID),0,1)<=10,

count({<ProductID,month,ProductName>}ProductID)/Count(TOTAL {<ProductID,month,ProductName>}ProductID))*avg(1)

Rank Expression

if(rank(total count({<ProductID,month,ProductName>}ProductID)/Count(TOTAL {<ProductID,month,ProductName>}ProductID),0,1)<=10,

rank(total count({<ProductID,month,ProductName>}ProductID)/Count(TOTAL {<ProductID,month,ProductName>}ProductID),0,1))*avg(1)

Re: Nulls in rank

Thu, 18 May 2017 10:26:27 GMT

Re: Nulls in rank

vinieme12 — Thu, 18 May 2017 10:26:40 GMT

see attached

Re: Nulls in rank

vinieme12 — Thu, 18 May 2017 10:27:23 GMT

check the app i attached!