Set Analysis Syntax Issue (Qlik Sense 3.2)?

[wdchristensen]

I have an issue where my set analysis formulas occasionally require tweaking for some preposterous reason.

Example 1: count({<SurgeryDate=${'>=(vStartOfCurrentFiscalYear)'} >} ID) /*filters as expected*/

Example 2: count({<Contact_Date=${'>=(vStartOfCurrentFiscalYear)'} >} ID) /*doesn’t filter; returns entire dataset*/

Example 3: count({<Contact_Date={'>=$(=$(vStartOfCurrentFiscalYear))'} >} ID) /*filters as desired but requires additional prepping */

I can eventually get the data to appear as I want with the method of trial and error. Why isn’t the syntax consistent? The only difference between Example 1 and Example 2 is the name of the various date fields, so why do I need to perform the alternate extra dollar sign expansion syntax? Worst case scenario when I can't get it to work in Qlik I can always use SQL but I would really like to learn the elusive mysteries of Qlik Sense 3.2. Any explanations would be greatly appreciated!

[sunny_talwar]

Dates in set analysis can be tricky... check here

Dates in Set Analysis

Also, check this for dollar sign expansion

The Magic of Dollar Expansions

[wdchristensen]

Hi Sunny, I appreciate the response. I agree set analysis can be tricky, but my real issue is with the inconsistency of the syntax. In my example (1 & 2), the set analysis formulas are identical except the name of the date field to filter is different. Why do I need a different syntax? Why can't I just switch out the date field name and be done? In what other way are the two scenarios different? Perhaps the answer lies within the links you sent but I didn't find it.

[Digvijay_Singh]

Strange!, $ in first two examples looks wrong to me. Are you sure there is no typo here?

[wdchristensen]

I copied and pasted my formulas so they should be correct. See the screenshot below:

[wdchristensen]

[Oleg_Troyansky · Accepted solution]

Hi William,

I hate to break it to you, but your last syntax shouldn't work. If it appears to be working, it is accidental.

A few pointers for you to consider:

- Set Analysis is mimicking the regular filter selection behavior. If you are searching for something that's not there, what is going to be the "correct" result - an empty set or the full set with no selections made?

- The difference between two fields with the same condition is hidden in the data. One field may have valid values in the desired range, and the other field may have no values that correspond to the search. The behavior can be different in these situations.

- Two different filters applied to the same field and listed one after the other in Set Analysis, will not return the intersection of the two, as you'd possibly expect. The second filter will replace the first one. So, in your example, it's only the "<" condition that's applied.

- Finally, I don't think that the syntax with the $-sign outside of the values {...} can work at all. If you are getting the results that you are expecting, it's most likely accidental. Unless you uncovered a hudden "feature" that's unknown to the world, this syntax is just wrong...

If you'd like to demystify Set Analysis, AGGR, and many other advanced development techniques and stop the guesswork, I encourage you to check out the agenda of the Masters Summit for Qlik. We teach all these advanced topics with a great deal of detail.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming to Prague in April!

[wdchristensen]

vStartOfCurrentFiscalYear is a variable in my example.

[Digvijay_Singh]

'$' cannot be outside {} in set expression, I am seeing for the first time, as per the documentation this syntax is not correct.

[wdchristensen]

Thank you all for your help figuring this out. Firstly, as I am sure you suspected I didn’t discover a previously unknown syntax for Qlik. I copied the syntax from a “working” application that I didn’t build. The application had a stored procedure which was filtering the data by the date on the load so the magical expression just happened to evaluate to true no matter what the date was. Because I believed it was performing the necessary filtering, I was totally stumped as to why the behavior was not repeatable but Oleg was right in that the “correct” result was merely a coincidence. So the assumption that the filter was actually filtering was the assumption that led me down this rabbit hole. I appreciate everyone that took the time to help me understand my issue. THANK YOU ALL!!!