Set Analysis Operators not calculating result correctly

[JohannEls]

Hi,

I would like to understand why the operators in the following KPI objects in Qlik Sense is not calculating the results correctly.  See image below.  

On the left you will find the data set and on the right you will find three KPI objects.  I have added the expression of each one of the KPI objects as a foot note and have also added the correct answer that I am expecting as a heading at the top of each KPI object.

I am running it on Qlik Sense Desktop November 2021 (14.44.5)

I am out of ideas - some of my students could get the correct results on older versions of Qlik Sense.

Any suggestions would be welcome!

Thanks

[JohannEls]

Unfortunately I do not have any connection to the students anymore.  The class was held yesterday.

[JohannEls]

I have also experienced this with a prior version earlier this year during the same course that I gave.
I have absolutely no idea what causes this inconsistency with Set Analysis in Qlik Sense.
Perhaps Qlik could shine some light on this issue...

[Or · Accepted solution]

I'm not sure what's going on behind the scenes with the Qlik engine, but I did notice that I get the incorrect values like you did using your formula, but if I replace EmployeeID with EmployeeName, I get the correct result. Perhaps there's an issue here related to aggregating a key field when applying set operators across multiple tables - I was able to generate similar problems using even the most basic set operators, for example the set here should return a list of all employee IDs but it only returns 1 through 9.

Presumably not coincidentally, these are all of the employees which have sales, so my guess is that the engine is counting out the EmployeeID from the OrderHeader or Territories table rather than the one in the Employees table. Perhaps an odd aspect of a multi-table key field with set operators (though this doesn't seem like it should be anything intentional to me)?

[JohannEls · Accepted solution]

I have now changed the count to rather count the EmployeeName as mentioned by "Or" and it calculated it perfectly. 

Here is my latest result:

Here is a peek at the Data Model:

[tresesco]

@Or  good catch! 

I have seen this behavior of key field analysis issue in set analysis earlier also. However, never thought that it could be an issue with versioning. The exact same application gave different results in different versions. @Or , could you please let us know the version of your qs?  

[Or · Accepted solution]

@tresesco I tested this with May 2021 Enterprise. I've never tried counting a key field before while also applying set operators (I don't typically need unions / intersects / etc), and I don't really understand why this would happen, but I can't think of any other explanation here. I'm not sure who at Qlik would be the right person to ask, either, or I'd tag them here.

@JohannEls  Please do keep in mind that counting employee names is not a safe approach, since two different employees might have the same name. Instead, I would suggest that you modify the dataset by adding a second, differently-named employee ID to the Employee table:

Load EmployeeID,

EmployeeID as EmployeeIDForCount,

[...]

From Employees;

You would then have an unambiguous field to count that isn't a key to any other table, and hopefully that should give you consistent and correct results.

[JohannEls]

100% - I agree!