Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Is it possible that two IDs in the top 20 have the same number for Sum({$<Status = {'Open'}>} Days)?
Is it possible that two IDs in the top 20 have the same number for Sum({$<Status = {'Open'}>} Days)?
May be check out this thread
Yes, it is possible. And, in fact, I just checked my data, and I do indeed have that very scenario. As usual, I'm sure this is where the business needs to step in to define how to handle ties. Once defined, is it best to implement this business logic via the mode argument in Rank?
Once defined, is it best to implement this business logic via the mode argument in Rank?
Most likely yes