Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

pbower1989
New Contributor

Filter Values using Fields as Variables in Set Analysis

Hi Everybody,

I have an application I am streamlining.

It has resulted in a need to create Master Measures with Field names as set analysis filters. Here is an example of a valid calculation (WITHOUT using fields):

sum({<[Policy Revision Group]={'18C and 18B'},[Product Code]={'API', 'ASI', 'AII'}>}[Renewed Paid])

Now, an except of my custom inline table of fields:

The 'PRG Filter' (Policy Revision Group) filter needs to sit in place of '18C and 18B' in the expression above. And likewise for the 'PC Filter' (Product Code) filter.

The end outcome is that the user will have a filter with only a few pre-defined selections that they can choose, by using a filter object based on the 'View' column (which makes it really easy for the Executive Audience who will just see the 'name' in the View column that they are already really familiar with).

Here is what I have so far, that doesn't work:

sum({<[Policy Revision Group]={${[PRG Filter]}},[Product Code]=${[PC Filter]}>}[Renewed Paid])

However it is not evaluating correctly.

I would really appreciate any help getting either this exact thing to work, or the same thing using Variables in the Set Analysis expression. Ideally just the field names (e.g. using dollar sign expansion).

Thanks heaps

Peter

3 Replies
vishsaggi
Esteemed Contributor III

Re: Filter Values using Fields as Variables in Set Analysis

Did you already defined the variables?

may be us like

LET vPRGFilter = Concat(GetFieldSelections([PRG Filter]), ',');

LET vPCFilter    = Concat(GetFieldSelections([PC Filter]), ',');

Then use like

Sum({< [Policy Revision Group] = {$(vPRGFIlter) }, [Product Code] = {$(vPCFilter) }   >})

Re: Filter Values using Fields as Variables in Set Analysis

To add more here it is

Sum({< [Policy Revision Group] = {$(=Concat(Chr(39) & GetFieldSelections([PRG Filter]) & Chr(39), ','))}, [Product Code] = {$(=Concat(Chr(39) & GetFieldSelections([PC Filter]) & Chr(39), ','))}>} [Renewed Paid])

Life is so rich, and we need to respect to the life !!!
gavinigovind452
New Contributor II

Re: Filter Values using Fields as Variables in Set Analysis

Hi All

please try below expression to get correct values

Sum({< [Policy Revision Group] = {$(=Concat(Chr(39) & replace(GetFieldSelections([PRG Filter]),',',chr(39)&','&chr(39)) & Chr(39), ','))}, [Product Code] = {$(=Concat(Chr(39) & Replace(GetFieldSelections([PC Filter]),','&chr(39)&','&chr(39)) & Chr(39), ','))}>} [Renewed Paid])

Community Browser