Summary: I suggest a simpler syntax for merging selections from multiple states.
You may be familiar with the “Product Grouping” example from the “What’s new in QlikView 11.qvw” sample. It’s a great beginner demo of using Alternate States for comparative analysis.
In this visualization, a user can select two sets of values from the [Product Sub Group] field. The two different sets are represented by two states, [Group 1] and [Group 2]. The blue [Group 1] bar is plotted on the bar chart using this expression:
sum({[Group 1]<Region = $::Region, [Sales Rep] = $::[Sales Rep], Path = $::Path, Year = $::Year, Quarter = $::Quarter, Month = $::Month>} Sales)
[Group 1] as the Set Identifier indicates we want to start with the selections in [Group 1]. We then modify, or add, selections from the default state by referencing each field with the “$::fieldname” syntax.
The green [Group 2] bar is created with a similar expression, the only difference being [Group 2] as the Set Identifier.
The “$::fieldname” syntax works, but it forces us to list every field. Listing every field can get difficult. Is there an easier, more generic method? Yes, if we redefine the problem as: All selections from the Default state except for [Product Sub Group].
sum({[Group 1] * $<[Product Sub Group]=>} Sales)
Breaking the Set Expression down:
[Group 1] // Group 1 selections
* // Intersected with
$<[Product Sub Group]=> // All selections from Default except [Product Sub Group]
I’m not suggesting the sample is wrong. The “$::” syntax is useful to know and is required when you want to reference only specific fields. I’m posting this alternative because I see people copying this more complex $:: syntax when the simpler syntax would suit their application.
-Rob
Join me at an upcoming Masters Summit for Qlik event in Johannesburg (6-8 Sept) or Austin (10-12 Oct). Oleg Troyansky’s Set Analysis and Advanced Aggregation session provides more useful tips and advanced techniques in using Alternate States.
Hi Rob
Might be a stupid question but is there any reason not to type the expression for the “whats new…” comparison as;
sum({$} Sales)
Hi Johan,
The point of the viz is to compare one set of [Product Sub Group] with another. “sum({$}Sales)” use the [Product Sub Group] from the Default selection, and there is no selection for [Product Sub Group] in the Default state. The selections are made in the two states [Group 1] and [Group 2].
Conceivably, the default state could have been used for one of the groups, simplifying the syntax for one group but not the other. I believe the author chose to use two new states for consistency,
Well I read your response and could quite understand youranswer until I saw that the expression in my question was incorrectly pasted. The expression in the question should have been;
sum({$[Product Sub Group] = [Group 1]::[Product Sub Group]} Sales)
NB! I have removed the greater than and less than signs.
I get it now! Yes, you could write it the way you suggested as well. Same idea — typing the smaller list of Alt State fieldname(s), instead of typing the longer list of Default State names.
Beautifil solution, Rob! Saved my day. 🙂