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!

[Oleg_Troyansky]

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