topic Re: Qlik Sense Top N with drillable dimension in App Development

Qlik Sense Top N with drillable dimension

skyline01 — Mon, 05 Mar 2018 17:20:31 GMT

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?

Re: Qlik Sense Top N with drillable dimension

isingh30 — Mon, 05 Mar 2018 17:58:25 GMT

Please share your data or app.

Thank you!

Re: Qlik Sense Top N with drillable dimension

skyline01 — Mon, 05 Mar 2018 18:01:07 GMT

As noted above, I can't share my app (because I am working in Enterprise). However, my data is attached to my original posting.

Re: Qlik Sense Top N with drillable dimension

jerryyang756 — Mon, 05 Mar 2018 18:28:27 GMT

Hi casey,

You are getting only top 9 because 'other' is enabled . Disable it. Please Check my attached application.

Re: Qlik Sense Top N with drillable dimension

skyline01 — Mon, 05 Mar 2018 18:41:19 GMT

Hi Pritam,

I am not identifying my top 10 vendors the same as you. As noted above, there is a one-to-many relationship between VendorID and VendorName. So, the top 10 identification has to be done by VendorID, not by VendorName. Hence, I am not able to implement the top 10 identification in the chart simply by the 'Limitation' option for the drill-down dimension. As far as I know, it has to be done in the measure definition. Still, though, VendorName is what needs to appear at the top tier of the drill-down dimension.