I'm trying to use Insight Advisor to filter some variables and I'm getting incorrect results. I was wondering if anyone has encountered this issue and has found a fix for it.
I have 3 variables:
vSupplier: Contains suppliers used by clients.
vSpend: Calculates the total spend with that client.
vYear: 2021, 2022, 2023.
I've asked Insight Advisor to give me the total spend for one supplier and one year. It says that this has been filtered but it is still giving me the total spend for all years and all suppliers.
Has anyone else encountered an issue like this, where despite Insight Advisor saying that it has applied filters on a varibale, it is clearly not the case?
@rosslyn-markgavin I know this is a few months late, but maybe it will help someone else out if you've already found the solution to your problem.
I'm not sure if our situation is the exact same situation as yours, since you didn't include your expressions for those variables in your question, but we had a similar outcome to the one you describe. I'll give you the short version of our setup and how we fixed it. (NOTE: We're on Qlik Sense Enterprise May 2023 version.)
We had a master measure called [Number of Cases] that is defined by a variable expression $(NumberOfCases). That NumberOfCases variable is defined as: num(sum({$<bunch_of_set_analysis>} Cases), '#,##0'). So, when we ask Insight Advisor (IA) to give us give us number of cases where Color is equal to BLACK, IA does what it does and sticks the set analysis to the front of the master measure, which looks like this: {<Color={'BLACK'}>} [Number of Cases].
So Qlik evaluates that as follows: {<Color={'BLACK'}>} num(sum({$<bunch_of_set_analysis>} Cases), '#,##0')
We finally found this doc that helped us understand the problem: it's the dollar sign ($) in the inner set expression. It was replacing the context, and then the inner set expression was taking precedence over the outer set expression.
From the "Inheritance" section of that doc:
If the inner set expression contains a set identifier, it replaces the context. Otherwise, the context and the set expression will be merged. {$<SetExpression>} - overrides the outer set expression {<SetExpression>} - is merged with the outer set expression
Note that the combination of inner and outer set identifiers is also potentially problematic, so it's probably best to go exclusively with outer set expressions moving forward, especially when IA is expected to be used, since that's apparently what it uses behind the scenes.
