topic set analysis formula comparable to sql's 'in' command? in QlikView

set analysis formula comparable to sql's 'in' command?

bekahbeets — Wed, 25 Nov 2020 16:16:04 GMT

I have set a variable equal to a field selection like so:

vSelectedYear = GetFieldSelections(YearField)

sometimes vSelectedYear will hold multiple values (2017, 2016, etc)

The set analysis formula looks like such right now:

sum( {<YearField = {"$(=$(vSelectedYear))"} >} [$(vType)])

this works fine if only one year is selected, but I would like it to work when vSelectedYear holds multiple years.

In Sql it would look like:

Select sum(vType)

from table

where YearField in (vSelectedYear)

swuehl — Mon, 10 Apr 2017 21:30:36 GMT

It should work if you remove the double quotes (assuming integer values for years):

sum( {<YearField = {$(=$(vSelectedYear))} >} [$(vType)])

Or use the p() function or the scope operator to directly acces field values selected or possible:

sum( {<YearField = $::YearField >} [$(vType)])

sum( {<YearField = p(YearField) >} [$(vType)])

(though latter set expression should not make a difference to just the pure aggregation?)

bekahbeets — Mon, 10 Apr 2017 21:42:16 GMT

Thanks! Removing the quotes did the trick!