topic Re: Negating Rank() sort criteria inserts Null entry into both List-Box & Pivot table Bottom-10 in QlikView

Negating Rank() sort criteria inserts Null entry into both List-Box & Pivot table Bottom-10 list

evan_kurowski — Sat, 16 Nov 2024 20:17:17 GMT

Hello Qlik Community,

I was taking a Set-Analysis course and doing fine until arriving at a section that used Rank() expression as set-analysis modifier.

The app had both a list-box & straight table set to show 'Top-10 Selling Products', and everything was working great.

=Aggr( Only( {<ProductName = {"=rank(Sum(LineSalesAmount)) <= 10"}>} ProductName), ProductName)

However, when I got the idea to switch to 'Bottom-10 Selling Products' by negating the sort criteria, that's when a null value caused both the list-box & straight table to go awry.

List-box: added a negative sign in front of LineSalesAmount
Straight table: reversed the dynamic sort from descending to ascending

I can clean up the Straight Table by checking 'Suppress When Null' and it resumes listing 10 entries, but then it becomes misaligned with the List-Box.

I'm confused why Null value gets introduced to begin with. The data model has one table association with no null values in the fields involved.

Both the list box & straight table are able to detect two Products with zero sales in the fact table, so the "missing" element is working. Where does that additional null come from?

The problem goes away when the data model is reduced to a single table, but once a single join is included, even if all fields involved are 100% populated, that null keeps showing up. Any ideas? Appreciate your help, ~E

Re: Negating Rank() sort criteria inserts Null entry into both List-Box & Pivot table Bottom-10

evan_kurowski — Fri, 09 Aug 2019 19:44:11 GMT

**addendum** - one quick correction, not all tables are reporting 'Perfect' key.. in the data modeler the Products table reports 'Perfect Key' for field [OrderID], but has 2 values not present in table 'Facts'. Hovering over [OrderID] in 'Facts' indicates 100% populated but does not indicate 'Perfect Key'.

Re: Negating Rank() sort criteria inserts Null entry into both List-Box & Pivot table Bottom-10

Brett_Bleess — Wed, 28 Aug 2019 12:41:57 GMT

Evan, you are likely going to need to attach sample qvw on this one, so folks can review the data model themselves and the objects and object properties in further detail...

The best I have as a means to potentially provide some further help is the following Design Blog post, and it contains some further posts at the bottom of the post, just FYI. It may give you something to help explain things, but I am not sure.

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

Sorry I do not have anything better for you, the only other thing would be the Help, as if you do see something not working as documented, then I would suggest opening up a support case at that point and providing us the details to replicate things at which point we would be able to potentially submit a software defect if we can replicate and there is not a fix in a newer SR for the track in which you are running.

Regards,
Brett