Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

Nested Set Modifiers with Advanced Searches

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

1 Solution

Accepted Solutions
Not applicable

Re: Nested Set Modifiers with Advanced Searches

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.

6 Replies
MVP
MVP

Re: Nested Set Modifiers with Advanced Searches

Try like:

sum(  {$<

Customer =

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

>} Sales )

Re: Nested Set Modifiers with Advanced Searches

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

Partner
Partner

Re: Nested Set Modifiers with Advanced Searches

Hi Robert,

I think this may help you.

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

Not applicable

Re: Nested Set Modifiers with Advanced Searches

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.

Re: Nested Set Modifiers with Advanced Searches

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

Not applicable

Re: Nested Set Modifiers with Advanced Searches

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.