topic Re: Comparative Analysis - Apply Filters to Two Groups (Alternate States) in QlikView

Comparative Analysis - Apply Filters to Two Groups (Alternate States)

Sun, 30 Dec 2012 00:04:45 GMT

Hello to everybody

I defined two Alternate States (Group 1 and Group 2).

Thanks to comparate analysis function I am able to set different filters for Group 1 and Group 2.

QUESTION: Is it possible to define some listboxes (or even multiboxes), which apply / affect both Groups?

In my case, I have three tables

- Customers (Name, Gender, Age, ...)

- Articles (Category, Colour, ...)

- Sales (combines customers and articles)

I would like to compare two different customer-groups in one chart. As I have written above; I am able to manage this. But as soon as I set filters to the article attributes, I have to do this twice for both groups (it should automaticlly affect both groups).

Is there a solution?

Thanks a lot in advance

Luc

Re: Comparative Analysis - Apply Filters to Two Groups (Alternate States)

Gysbert_Wassenaar — Sun, 30 Dec 2012 08:52:45 GMT

You can use set analysis expressions to specify which states apply to which fields. See attached example. It contains two alternate states Group 1 and Group 2. The field Year is set per state. In the top charts the selections in Product are not used. In the lower charts the selections in Product are applied to all charts, regardless of state.

Re: Comparative Analysis - Apply Filters to Two Groups (Alternate States)

Sun, 30 Dec 2012 13:43:58 GMT

Hi Gysbert Wassenaar

Thanks a lot for your answer. That works fine.

Is it possible to apply the same mechanism even to whole MULTIBOXES (and not only to the attribute product, which is listed manually in the expression)?

Thanks

Luc

Re: Comparative Analysis - Apply Filters to Two Groups (Alternate States)

Gysbert_Wassenaar — Sun, 30 Dec 2012 13:58:46 GMT

If you have only a few fields which should 'listen' to states then don't assign states to the charts. Only use the states in the expressions for those few fields. Instead of sum({[Group 1]<Product=$::Product>}Sales) you would use expressions like sum({<Year=[Group 1]::Year>}Sales). You then only specify the state for the fields that should 'listen' to that specific state.

So you could add all the fields in your multibox with $::fieldname to assign them the default state or you can turn it around and use [GroupName]::fieldname to assign the fields that should not use the default state to the alternate state of your choice.

Re: Comparative Analysis - Apply Filters to Two Groups (Alternate States)

Sun, 30 Dec 2012 14:11:36 GMT

Hi Gysbert

Thanks again for the fast reply. I have the following situation

Table Customers has 82 fields
Table Articles has 38 fields
The number of the fields and their names can change over time (I will set up different projects which use a very similar data cockpit but the fields and their names can be slightly different from project to project)
I would like to compare two customer groups (e.g. male, 30-40years old, low income vs. male, 40-50years old, high income)
The filters for articles should apply to both groups

Due to the high number of fields and their 'instability' over time it is not very handy to hardcode the names of the fields in the expression statements. It would be a lot easier if I could add them to a multibox...

Any ideas?

Thanks

Luc

Re: Comparative Analysis - Apply Filters to Two Groups (Alternate States)

Gysbert_Wassenaar — Sun, 30 Dec 2012 20:37:29 GMT

If your fields keep changing without warning you're in for a world of pain.

You could put all your article fields into a multibox and use the default state for the multibox. If your chart is also set to use the default state then selections in those fields will be applied to your chart.

You should probably create a new field (called FieldList for example) that contains the names of all the fields you wish to choose from for comparison of the two groups. You could add this at the end of your script:

for ii=0 to NoOfTables()-1

for jj=0 to NoOfFields(TableName($(ii)) )

FieldListTable:

LOAD FieldName($(jj),TableName($(ii))) as FieldList

Autogenerate 1;

This will create a table called FieldListTable with a field FieldList that will contain the fields of your document (excluding itself). You can then a a listbox for FieldList so you can select fieldnames for the comparison. Next you use expressions like below to tie the selected fields to an alternative state (Group 1 in the example below).

sum({$< $(=Concat( distinct '[' & FieldList & ']=[Group 1]::[' & FieldList & ']',',' )) >} Sales)

You'll still have to create listboxes or multiboxes per group for the fields to be able to make selections. I've explored one limited way to populate multiboxes dynamically. It's not perfect, field names with spaces can't be used for example. Perhaps a macro based solution is a better option.

Re: Comparative Analysis - Apply Filters to Two Groups (Alternate States)

rotero — Thu, 01 Aug 2013 01:41:44 GMT

thanks for this..very helpful..

Re: Comparative Analysis - Apply Filters to Two Groups (Alternate States)

Fri, 10 Oct 2014 10:51:06 GMT

Hello Team

i am using the following syntax for set analytics, it is not working fine. kindly let me know the correct syntax.But the values are not matching with the back end.

sum({<CUSTID-={"[ALP]::CUSTID}">}TOTALSALE)

Desc

[ALP] is one group