Filter Values using Fields as Variables in Set Analysis

[Anonymous]

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

[vishsaggi]

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

[Anil_Babu_Samineni]

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

[gavinigovind452]

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