4 Replies Latest reply: Mar 5, 2018 1:41 PM by Casey Marincin RSS

    Qlik Sense Top N with drillable dimension

    Casey Marincin

      I am working in Qlik Sense 3.2 Enterprise (hence, no qvf attached).  I have only one table in my data model.  The table's columns are:

      TransactionID

      Amount

      VendorID

      VendorName

      Manager

       

      I have attached my data.


      All columns are populated for every row.  TransactionID is my primary key.  In general (though possibly not in my attached data), VendorID has a one-to-many relationship with VendorName, and VendorID has a many-to-many relationship with Manager.

       

      I need to create a bar chart of Sum(Amount) versus a drillable dimension of VendorName \ Manager for the top ten vendors (by VendorID).  It is permissible to display a "random" VendorName for each of these ten vendors (e.g., display the first associated VendorName encountered for each VendorID).  I have created the given drillable dimension as a master dimension.  My measure definition is:

       

      If(

           Aggr(Rank(Sum(Amount)), VendorID) <= 10

          ,Sum(Amount)

           ,Null()

      )

       

      I am getting two unexpected results / behavior.  One, instead of the top ten vendors, I only see nine vendors.  Two, when I drill the dimension, I am getting an incomplete set of Managers returned for each VendorName.  There are no ties in the data (i.e., there are unique sums of Amount for each VendorID), and there are more than ten vendors in the data.

       

      I'm not sure what I'm doing wrong.  I suspect that it's being caused by the one-to-many and many-to-many relationships in my data.  How can I resolve these two issues?