Nested Set Modifiers with Advanced Searches

[#1]

Hi All,

I have a set analysis statement that looks like this:

{<Date_Key = {"$(= '>=' & date(addmonths(max(MonthYearShort), -2)) & '<=' & date(max(MonthYearShort)))"} >}

This is to select the most recent 3 months of any given selection and show results based on that.

However I am running into a problem whenever I try to use this kind of function within another set analysis statement. The Qlikview documentation has the following example of set modifers with advanced searches:

sum(  {$<
Customer =
  {“=Sum({1<Year = {2007}>} Sales ) > 1000000”}
>} Sales )

I need this modified to look something like this:

sum(  {$<
Customer =
  {“=Sum({1<Date_Key = {"$(= '>=' & date(addmonths(max(MonthYearShort), -2)) & '<=' & date(max(MonthYearShort)))"} >} Sales ) > 1000000”}
>} Sales )

The problem here seems to be the repeated use of the double quotation marks. When this expression is written into Qlikview, the double quotes at the start of the Customer statement, {“=Sum, seems to close with the double quotes at the start of the Date_Key statement, Date_Key = {"$(= '>='.

Of course, the example above in the Qlik references uses a number which doesn't require any quotations, however I'm needing the date value evaluated based on the above expression.

Does anyone know of any solution or workaround to this problem? I've played around with some dollar sign expansion without success, but I'm wondering if this is indeed the solution.

Many thanks!!

[tresesco]

Try like:

sum(  {$<

Customer =

  {“=Sum({1<Date_Key = { '>=$(=date(addmonths(max(MonthYearShort), -2))) <=$(=date(max(MonthYearShort)))'} >} Sales ) > 1000000”}

>} Sales )

[sunny_talwar]

Or may be using a Escape sequences might help. Have not tested, but something to look into

[dineshraj]

Hi Robert,

I think this may help you.

sum(Date={">=$(=Date(Addmonths(Max(Date),-2)))<=$(=Date(Max(Date)))"} field_name)

[#5]

Hi all,

Thanks for the replies and sorry for not following up on this sooner.

Unfortunately none of the suggestions so far seem to be working so I'm going to experiment a bit more with this and I will post a solution once I've worked it out.

[sunny_talwar]

Experimenting is good, but if you still have issue you can share a sample so that we can see what issue you are running into.

Preparing examples for Upload - Reduction and Data Scrambling

[#7 · Accepted solution]

Hi all,

So this is what my solution eventually looked like:

=Count(DISTINCT{<[ProductID] =
{"=(((sum({$<[ProductField1] = {[value]}, [ProductField2]={[value]}, Date_Key = {'>$(=date(addmonths(max(MonthYear),-4),[MMM-YY])) <$(=date(max(MonthYear),[MMM-YY]))'}>} ProductField3)
+
sum({$<[ProductField1] = {[value], [value]}, [ProductField2]={[value]}, Date_Key = {'>$(=date(addmonths(max(MonthYear),-4),[MMM-YY])) <$(=date(max(MonthYear),[MMM-YY]))'}>} ProductField3))
/3)/[ProductField4])
> 1.1"}
>} ProductID)

Pay attention to the square brackets used for some of the fields and values, as opposed to quotation marks.