Solved: Set Analysis Operators not calculating result corr... - Qlik Community

JohannEls · ‎2021-11-25

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

Or · ‎2021-11-25

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)?

View solution in original post

JohannEls · ‎2021-11-25

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:

View solution in original post

Or · ‎2021-11-25

@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.

View solution in original post

tresesco · ‎2021-11-25

Could you share your qvf?

JohannEls · ‎2021-11-25

Hi,
No problem - please find app attached below.

Incorrect App has been deleted - find correct App download in later chat below.

tresesco · ‎2021-11-25

I see in my desktop (Nov 2020) as:

JohannEls · ‎2021-11-25

I see that your fourth KPI from the top is also still calculating it incorrectly. Is there perhaps a setting on Qlik Sense desktop that you might think that could cause this?
In my class on Set Analysis - some students get the same incorrect results as I do and some get the correct results and some partially correct results.
Here is the screen pic on my side again:

tresesco · ‎2021-11-25

Your third KPI expression is wrong; it is not the same as being shown on the footer.

JohannEls · ‎2021-11-25

Sorry - saw that just now - still one of the tests that I have done - just change the last KPI's Expression to what is in the footer. I will do the same on my side and share the screen pic.

JohannEls · ‎2021-11-25

JohannEls · ‎2021-11-25

Here is the app again with the correction done on the bottom KPI. But it is still giving me the same incorrect results.

tresesco · ‎2021-11-25

I can't test on Nov 2021 version now. What about the versions and results your class mates are using?

Set Analysis Operators not calculating result correctly

General Question