topic Re: Dimensions as variable in set analysis in QlikView

Dimensions as variable in set analysis

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

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

Re: Dimensions as variable in set analysis

marcus_sommer — Fri, 29 Apr 2016 14:29:27 GMT

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

Re: Dimensions as variable in set analysis

Gysbert_Wassenaar — Fri, 29 Apr 2016 14:32:33 GMT

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)

Re: Dimensions as variable in set analysis

polisetti — Fri, 29 Apr 2016 14:46:02 GMT

Thanks Gysbert,

I will let you know if this works for me.

Re: Dimensions as variable in set analysis

triekong — Wed, 22 May 2019 18:37:59 GMT

@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

Re: Dimensions as variable in set analysis

Gysbert_Wassenaar — Thu, 23 May 2019 06:05:53 GMT

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.