Dimensions as variable in set analysis

[polisetti]

Hi everyone,

I have different time period flags in my data model as R3M, R6M, R12M with 'C'(Current) and 'P'(Previous) flag populated for each time filter. Currently, I am calculating only for R3M flags, so I wrote set analysis like below.

"Sum({<R3M={'C'}>}Sales)"

Now we are planning to give time period filter on top of dashboard like R3M, R6M, R12M. By selecting on each time period list box the dashboard should be filtered out. So instead of writing multiple expressions in the chart, we need to make R3M, R6M, R12M dimensions values into the variables and by changing the filter selection the value in the set analysis should need to change.

So Please guide me how to assign different fields(R3M,R6M,R12M) into one list box and driving the values into expressions.

=Sum({<$(vVariable)={'C'}>}Sales)??

Thanks in Advance,

Polisetti

[marcus_sommer]

In general you could replace it with a variable. Here an example with a selection of a field [Category] which will be evaluated per $-sign expansion:

=Sum({< $(='[' & getfieldselections([Category]) & ']') ={'C'}>}Sales)

- Marcus

[Gysbert_Wassenaar]

Create a small inline table with a field for the listbox:

NewTable:

LOAD * INLINE [

ListBoxField

R3M

R6M

R12M

];

Use that listbox to select one value. Enabling the Always One Selected Value option of the listbox might be a good idea.

Then you can use this expression: sum({<$(=Only(ListBoxField))={'C'}>}Sales). If you want you can also put $(=Only(ListBoxField)) into a variable: =Only(ListBoxField). And use the variable in the expression: sum({<$(vMyVar)={'C'}>}Sales)

[polisetti]

Thanks Gysbert,

I will let you know if this works for me.

[triekong]

@Gysbert_Wassenaar @polisetti what connects the new INLINE table you created and the previous data table you already had? For example, when i select 'R3M' in the INLINE table, how does it know that i am selecting the 'R3M' in the original table? 

i think i'm missing something. please guide. thanks

[Gysbert_Wassenaar]

Nothing connects the inline table with the rest of the data model. It's a data island table. You merely used the inline table to let the user select a value. That value is then used to dynamically change the set analysis expression to select the value C in the field that the user selected.