topic Re: Filter Values using Fields as Variables in Set Analysis in QlikView

Filter Values using Fields as Variables in Set Analysis

Anonymous — Thu, 14 Dec 2017 22:08:36 GMT

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

Re: Filter Values using Fields as Variables in Set Analysis

vishsaggi — Thu, 14 Dec 2017 22:33:20 GMT

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

Anil_Babu_Samineni — Fri, 15 Dec 2017 01:58:52 GMT

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])

Re: Filter Values using Fields as Variables in Set Analysis

gavinigovind452 — Tue, 21 Aug 2018 14:39:49 GMT

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])