topic Re: Text Objects with rank info not working as expected. in QlikView

Text Objects with rank info not working as expected.

pgriffiths — Thu, 16 Jul 2015 16:16:35 GMT

I am trying to display rank information on restricted application installs in text objects instead of a straight table.

Each box needs to display the next ranked item.

I have the following sort of working.

='Rank3: '

&only(if(aggr(Rank(sum(ApplicationRestricted)), ApplicationName)=3, ApplicationName))

&' '&only(if(aggr(Rank(sum(ApplicationRestricted)), ApplicationName)=3, Aggr(sum(ApplicationRestricted), ApplicationName)))

This would output something like

Rank1: Dropbox 104
Rank2: Evernote 87
Rank3: Box Sync 7

etc...

The problem I am having is when it gets down to items with the same amount, in my case, items with just 1 install each, the script stops working and outputs nothing.

I was expecting the other text objects to still display like the following but instead they are blank.

Rank4: Transmission 1
Rank5: BitTorrent 1

etc...

I am wanting to display each item in descending order, much like I would with a straight table but using Text Objects to display the data.

Anyone got any idea how to fix this or on a different approach?.

Many thanks.

Re: Text Objects with rank info not working as expected.

hic — Thu, 16 Jul 2015 16:31:34 GMT

You need to look at the 2nd and 3rd parameter of the Rank function. The second defines which rank should be used for multiple identical values, and the 3rd how the result should be formatted.

You probably want Num(Rank(..., 4))

HIC

Re: Text Objects with rank info not working as expected.

antoniotiman — Thu, 16 Jul 2015 16:34:16 GMT

Hi Peterjohn,

Only() function returns Null when Field (or Expression) has many values.

Try to use Sum(DISTINCT....) and Rank(expression,0,1)

Regards,

Antonio

Re: Text Objects with rank info not working as expected.

pgriffiths — Mon, 20 Jul 2015 09:41:25 GMT

Thank you Henric, that works great and the example output helped me understand why better than the help file.

Many thanks