topic Strange behaviour of Rank function in QlikView

Strange behaviour of Rank function

svanderwerf — Thu, 22 May 2014 07:21:11 GMT

For the last two days I have been struggling with some strange behaviour of the Rank function:

I have a straight table which shows a score for different companies, on a certain item. This score is calculated with the following expression:

sum({< ItemCode = {'General_01'} >} Score * Weight) / sum({< ItemCode = {'General_01'} >} Weight)

I am using the same expression in my Rank function:

Rank((sum({< ItemCode = {'General_01'} >} Score * Weight) / sum({< ItemCode = {'General_01'} >} Weight))).

This results in the following table:

Company	Score	Rank

Company 1	4,111111111	1-2
Company 2	4,111111111	1-2
Company 3	3,76	3
Company 4	3,387096774	4
Company 5	3	5
Company 6	2,8	6

As you can see, Company 1 and Company 2 share positions 1 and 2 in this ranking.

Now, when making a selection in a field, which does not in any way change the result set, the table looks as follows:

Company	Score	Rank	sum(Score)	# of rows	sum(weight)

Company 1	4,111111111	1	111	27	14,734320624349
Company 2	4,111111111	2	111	27	15,078983095094
Company 3	3,76	3	94	25	13,532420726367
Company 4	3,387096774	4	105	31	24,126372952151
Company 5	3	5	6	2	5,5848085537386
Company 6	2,8	6	14	5	5,6965047248133

The score stays exactly the same, but the ranking of Company 1 and Company 2 changed from 1-2 to 1 for Company 1 and 2 for Company 2.

I added columns to show the sum of Score and the sum of Weight fields. The values in these columns do not change when I change the selection.

What am I missing here? (I already tried fiddling with the parameters for format and mode in the Rank function, but this did not solve the issue).

Additional info: I am using QV 11.2 SR6 64 bits Desktop on Windows 8. The dataset I am using is fairly large (aprox. 100M rows).

Please help!

Regards,

Stevan

Re: Strange behaviour of Rank function

Thu, 22 May 2014 07:40:54 GMT

Steven,

but first two rows have different sum(weight).

May you share example app?

regards

Darek

Re: Strange behaviour of Rank function

svanderwerf — Thu, 22 May 2014 09:39:41 GMT

Hi Darius,

Unfortunately I cannot share an example (due to confidentiality agreements).

But, you are correct: the weight is different on the first two rows. But in this case the weight on all rows for each company is the same. Changing the selection in the way I mentioned does not change the result for 'Weight' and thus it does not change the Score (and should not change the ranking too).

The weight for all rows for Company 1 is 0,545715 and for Company 2 the weight is 0,5584 for each row.

But whatever weight it would be, the selected rows for the rank function do not change when changing the selection, so the actual outcome of the score does not change.

Any other ideas?

Regards,

Stevan

Re: Strange behaviour of Rank function

Thu, 22 May 2014 09:44:11 GMT

Steavan,

prapare sample with this one dashbord, 1 expression and random data but working as you described.

it will be easier to found reason.:)

regards

Darek

Re: Strange behaviour of Rank function

svanderwerf — Thu, 26 Jun 2014 09:36:56 GMT

Hi Dariusz.

My apologies for not getting back to you sooner.

Unfortunately I am not able to reproduce this error in a sample dashboard with random data. It seems that simplifying my datamodel (i.e. cutting out QV-tables that only serve as link tables), ends this behaviour.

Many thanks for your help so far!

Regards,

Stevan