Problem with "rank" and suppress null values

[#1]

Hello,

I have a problem with my QVW in attached.

I have simplified the example and I cannot suppress the 0 values in the script (because qty are linked to a date and I filter the date using a calendar in the report).

I have:

- Item

- Seller

- Quantity

I want to sum the quantities by Item and Seller and show the line ONLY if the sum of Qty is not equal to 0.

It is OK with 3 columns (Item, Seller and sum of Qty), but if I add a rank column, the lines with null values appear (the suppress null values is checked).

How can I suppress thes lines and keep my rank columns ?

Thank you,

Willy

[#2]

Yep, but it is checked

That is my problem.

On server side it is KO too.

I will have a look tomorrow on another PC.

Thank you All,

Willy

[swuehl]

Which QV version are you running on the PC that shows the issue?

[#4]

Hi settu_periasamy,

thank you for testing.

Here is what I see:

Same thing

Willy

[#5]

Hi again swuehl

Here is your answer:

I do not understand why when I remove the rank column, null values are suppressed and when I add it there do not

I have lost so many time on my 2 questions of the day that I need teh community help.

I have searched before asking.

Regards,

Willy

[swuehl]

Just have a look into the HELP, rank() function:

Note!
Suppression of zero values is automatically disabled when these functions are used. NULL values are disregarded.

Using a more recent QV version (like 11.20 SR11 x64 on my PC), this (i.e. suppression of zero values) seems to work, though.

Seems like something has changed since the old days the Help was written (and I remember this limitation very well).

edit:

I think that has been added now to the HELP statement: NULL values are disregarded.

[#7]

Hello swuehl,

I tried with that expression:

=If(

  Sum(Qty)>0

  ,Rank(Sum(Qty))

  )

So, there is no null value in my Rank function now --> Same result

[swuehl · Accepted solution]

What I've intended to say:

'Suppressing of zero values' will be disabled when using certain functions, like the rank().

It seems that there was a change in recent version (not in your 10.00), that handles at least NULL values differently (maybe also zero values). I believe that's why settu, Sunny and myself can't reproduce your issue on our machines.

I am not 100% sure if the change was also documented, could you post your 10.0 documentation line for that Note! I've posted above (just look into the Help for rank(), this line should be top of the page)?

I also believe it has nothing to do with NULLs in the rank function, but returned NULLs and zeros and usage of the 'supress zero values' feature.

Sorry, if this is getting complicated.

You said, on server side it's KO, too. That means it's not working, right? Same 10.00 version on that server?

Any chance to test on a  more recent major version?

[#9]

Hi swuehl,

I think that you are right (it is KO on server side in QV10 too).

We have tested on QV11 and the lines do not appear.

I will wait our QV migration to QV 11 (in 1 month) instead of searching.

Thank you for all,

Willy

[swuehl]

Good to hear that you have a chance to migrate in near future.

Regards,

Stefan