topic Use macro to build up set analysis variable from selected values in QlikView

Use macro to build up set analysis variable from selected values

renem — Tue, 07 Jul 2009 02:54:42 GMT

I want to be able to build up the Set Analysis string and place it in a variable based on the current selections...

ie. A user clicks a button and the macro gets all the current selections and then builds up the set analysis string from this eg. sum( {1<Year={"2005"},Month={"jan","feb"},CategoryName={"b*"},ProductName<>{"Ballet Shoes"}>] Amount)

I want to replace this with sum($(vSetAnalysis) Amount) and set the variable vSetAnalysis in the macro

Use macro to build up set analysis variable from selected values

Tue, 07 Jul 2009 03:08:36 GMT

To get the selections in a field, you can use GetFieldSelections(FieldName). This will return a comma separated list of the selected values for a particular field.

As for your vSetAnalysis variable, it would need to be defined as a concatenation of your fields and GetFieldSelections results.

When no values are selected in a field, you would need a modification as that would get you a null value. You could use something like If(IsNull(GetFieldSelections(FieldName), '*', GetFieldSelections(FieldName). That way if nothing is selected, then you would look for anything. I would think that is the behavior you want, but that depends on your data and app.

I don't know if there is a way to automatically create the field list for you. It should be possible using a macro and the API to loop through possible fields and creare that Set Analysis string. If you're going to pre-define all the fields that need to be included, then it can probably be done without a macro, although it will probably end up being a very long variable definition.

Use macro to build up set analysis variable from selected values

Thu, 20 Aug 2009 15:48:35 GMT

Hi renem - I'm trying to achieve something similar to this - have you managed to resolve it?

Use macro to build up set analysis variable from selected values

Anonymous — Thu, 20 Aug 2009 16:03:27 GMT

If you need the full list of values when nothing is selected you can use the concat() function.

=Concat(Field,';')

The first parameter is the list to concatenate and the second parameter is the separator between the values.

//jsn