topic Re: Set analysis inside another set analysis in QlikView

Set analysis inside another set analysis

Fri, 22 Jan 2016 10:24:51 GMT

Hi, is it possible to have an expression with set analysis, and inside this one another set analysis? I'm triyng to use this expression but it doesn't work:

=SUM({<[DateProy] = {'>=$(=max({<A_Date = {<=$(=Date(vEndDate))}>}A_Date))<=$(=Date(vEndDate))'}>}[P_Hours])

Thanks

Re: Set analysis inside another set analysis

sunny_talwar — Fri, 22 Jan 2016 11:19:57 GMT

It is possible, may be try this:

=SUM({<[DateProy] = {">=$(=Date(Max({<A_Date = {'<=$(=Date(vEndDate))'}>} A_Date)))<=$(=Date(vEndDate))"}>}[P_Hours])

Re: Set analysis inside another set analysis

sasikanth — Fri, 22 Jan 2016 11:21:21 GMT

Hi,

check A-Date and vEndDate formats ,

if possible please share your application

Re: Set analysis inside another set analysis

Fri, 22 Jan 2016 13:30:11 GMT

Thanks, this works, but only if I filter a value in the table's dimension. It seems that the dimension doesn't affect the inner set analysis...

Re: Set analysis inside another set analysis

Anonymous — Fri, 22 Jan 2016 13:37:47 GMT

Missing Quote..try like this?

=SUM({<[DateProy]=,[DateProy] = {'>=$(=max({<A_Date = {'<=$(=Date(vEndDate))'}>} A_Date))

<=$(=Date(vEndDate))'}>}[P_Hours])

Just modifying as Max() return integer

=SUM({<[DateProy]=,[DateProy] = {'>=$(=date(max({<A_Date = {'<=$(=Date(vEndDate))'}>} A_Date)),'YourDateFormat')

<=$(=Date(vEndDate))'}>}[P_Hours])

Also as Sunny kmentioned cross check for format as well

Re: Set analysis inside another set analysis

sunny_talwar — Fri, 22 Jan 2016 13:50:11 GMT

Single quotes within single quotes is not going to work either

and to get the format right (if it needs to be fixed) the expression will need to be much more complicated for the same reason that we cannot have single quotes within single quotes and to give the format we need single quotes around the format.

To save the trouble of specifying the format, I would use Date() function without format in the script for both DateProy and A_Date in the script.

Re: Set analysis inside another set analysis

Oleg_Troyansky — Fri, 22 Jan 2016 14:00:51 GMT

A few general observations, as there seems to be a lot of confusion here:

- Nested Set Analysis is possible, no problem there.

- Single quotes should not be used for Search. Currently it works but only by accident. The recommendation from R&D is to alternate double quotes " " with square brackets [ ].

- In order to avoid any date formatting issues, you can replace Simple Search with an Advanced Search. For example:

Replace this simple search condition:

sum( {<Date={">=$(=max(AnotherDate))"}>}

with this Advanced Search condition:

sum( {<Date={"=Date>=$(=max(AnotherDate))"}>}

and any formatting issues disappear.

For anyone interested in learning advanced Set Analysis - I teach all these techniques, and many others, at the Masters Summit for Qlik. Coming this year to Milan, Italy, South Africa, and Austin, TX. Come and upgrade your Qlik skills!

Re: Set analysis inside another set analysis

Oleg_Troyansky — Fri, 22 Jan 2016 16:22:45 GMT

One more general thing to know about Set Analysis - its condition is calculated only once, before the chart is being calculated, therefore Set Analysis conditions cannot be sensitive to the specific values of the Chart Dimensions.