topic Re: Nested Set Modifiers with Advanced Searches in QlikView

Nested Set Modifiers with Advanced Searches

Mon, 29 Aug 2016 10:37:58 GMT

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

Re: Nested Set Modifiers with Advanced Searches

tresB — Mon, 29 Aug 2016 12:14:14 GMT

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

sunny_talwar — Mon, 29 Aug 2016 12:28:01 GMT

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

Re: Nested Set Modifiers with Advanced Searches

dineshraj — Mon, 29 Aug 2016 12:33:10 GMT

Hi Robert,

I think this may help you.

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

Re: Nested Set Modifiers with Advanced Searches

Thu, 08 Sep 2016 09:30:52 GMT

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

sunny_talwar — Thu, 08 Sep 2016 15:48:44 GMT

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

Re: Nested Set Modifiers with Advanced Searches

Thu, 29 Sep 2016 09:45:13 GMT

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.