4 Replies Latest reply: Jan 23, 2018 7:03 PM by Sunny Talwar RSS

    top N filter returning more than N data points

    Casey Marincin

      I'm working Qlik Sense 3.2.  I have a single internal data table, with the following attributes:

      ID

      Region

      Status

      Days

       

      I need to create a bar chart of Count(ID) vs Region.  The data for this bar chart must be filtered to rows where Status = 'Open'.  The data must be further filtered to the top 20 rows by Days.  I have implemented the following for my measure in the bar chart:

      =Count(

      {

      $<ID = {"=Rank(Sum({$<Status = {'Open'}>} Days)) <= 20"}>

      }

      ID

      )

       

      Oddly enough, when I manually check the sums of counts across all dimension (Region) values, I get count = 21, not 20.  Everything else on the bar charts looks as expected, though (e.g., no unexpected Region values).  So, I tried different visualization types (e.g., table), and I get the same result.  So, the issue is not visualization type - specific.  I'm guessing it's due to a unique scenario in my data.  Unfortunately, I can't post my data.  (It's proprietary.)  To help simulate my data set to find the scenario, I profiled my data on all 4 attributes.  I'm not seeing anything unique, other than 2 rows where ID is null for Status <> 'Open'.  What could I be doing wrong that is resulting in 21 data points, rather than the expected 20?