topic Set Analysis Syntax Issue (Qlik Sense 3.2)? in App Development

Set Analysis Syntax Issue (Qlik Sense 3.2)?

wdchristensen — Thu, 08 Feb 2018 20:11:32 GMT

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!

Re: Set Analysis Syntax Issue (Qlik Sense 3.2)?

sunny_talwar — Thu, 08 Feb 2018 20:16:42 GMT

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

Re: Set Analysis Syntax Issue (Qlik Sense 3.2)?

wdchristensen — Thu, 08 Feb 2018 20:56:14 GMT

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.

Re: Set Analysis Syntax Issue (Qlik Sense 3.2)?

Digvijay_Singh — Thu, 08 Feb 2018 21:03:53 GMT

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

Re: Set Analysis Syntax Issue (Qlik Sense 3.2)?

wdchristensen — Thu, 08 Feb 2018 21:15:02 GMT

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

Re: Set Analysis Syntax Issue (Qlik Sense 3.2)?

wdchristensen — Thu, 08 Feb 2018 21:28:10 GMT

Re: Set Analysis Syntax Issue (Qlik Sense 3.2)?

Oleg_Troyansky — Thu, 08 Feb 2018 21:45:00 GMT

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!

Re: Set Analysis Syntax Issue (Qlik Sense 3.2)?

wdchristensen — Thu, 08 Feb 2018 22:41:25 GMT

vStartOfCurrentFiscalYear is a variable in my example.

Re: Set Analysis Syntax Issue (Qlik Sense 3.2)?

Digvijay_Singh — Thu, 08 Feb 2018 22:58:24 GMT

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

Re: Set Analysis Syntax Issue (Qlik Sense 3.2)?

wdchristensen — Fri, 09 Feb 2018 15:32:24 GMT

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!!!

Re: Set Analysis Syntax Issue (Qlik Sense 3.2)?

Oleg_Troyansky — Fri, 09 Feb 2018 15:43:10 GMT

Yes, I got that... Perhaps the syntax that you were shooting for, is:

count({<SurgeryDate={">=$(vStartOfCurrentFiscalYear)"} >} ID)

Notice:

1. $-sign is within the {"..."} filter, forming a $-sign expansion.

2. This syntax can only work if both the date field and the variable are numeric (not formatted dates)

3. For formatted dates, you need to make sure that the data type is right. One of the following two can help:

count({<SurgeryDate={">=date('$(vStartOfCurrentFiscalYear)')"} >} ID)

count({<SurgeryDate={">='$(vStartOfCurrentFiscalYear)'"} >} ID)

4. Also notice that we use the double quotes for the search and single quotes for a $-sign expansion that should return a formatted date.

Finally, in order to apply two filters together, the following is going to work:

count({<SurgeryDate={">=date('$(vStartOfPreviousFiscalYear)')<date('$(vStartOfCurrentFiscalYear)')"} >} ID)

You can learn all that, and a lot more, at the Masters Summit for Qlik. If you can't make it to the Summit, check out my book QlikView Your Business. I explain advanced Set Analysis with a lot of details there.

cheers,

Oleg Troyansky