I recently had my first opportunity to use Alternate States on a real project. As I was developing the expressions for my charts, I came across a predicament. It represents the entire data model PLUS all user selections. In other words. My reasoning on this was to prefer the explicit over the implicit, i. Additionally, any expression in any object can either inherit the state of the object or be controlled via set analysis. This ignores the alternate states. Contrast this to omitting the dollar-sign: sum Sales In this case we are accepting whatever state the object is in.
And two groups of list boxes, one State A and the other in State B.
Each chart had several expressions and included set analysis for other calculation reasons. Hence, to my chagrin, when I clicked on values in my State A and State B list boxes, nothing changed. Posted in Uncategorized. You can follow any responses to this entry through the RSS 2.
Both comments and pings are currently closed. Thanks for this post, Bill. I first heard about alternate states just today. Your post will help me to avoid making the same mistakes you made. Thank you for sharing, Bill!
Set Analysis & Alternate States – Quick Tip
Someone asked me this question few days ago, and your article was very helpful to resolve the issue. Thanks Bill. After a couple of hours of head scratching you have just resolved why my charts are not showing the alternate state data.
Practical posts for QlikView fanatics! Search for:. Dean says:. March 29, at pm. Shilpan says:. June 20, at am. Trisha says:. January 7, at am.
Of all the training and conferences I attended for QlikView this has been above and beyond the best one to date. It was much more than I expected. Lots of tips and tricks, ideas and experience exchange.Set Analysis is a powerful set of syntax which is widely used in QlikView and Qlik Sense applications, but I find that many developers are unaware one of the most useful functions.
Here I explain how to use it. Qlik developers have a lot of syntax to learn. Set Analysis is a subset of expression syntax that can cause some developers issues. It is however very simple at heart. You can add a Set to an expression which changes the selection context for that expression — effectively forcing selections that only apply to that one expression.
By nesting Set Analysis syntax, and using different operators, highly complex expressions can be built. They are all however just expansions on the very simple concept of applying selections on a field.
One of the problems with Set Analysis is that the results can be counter Intuitive to users. When Qlik products are explained to them, they are told that their selections effect everything.
Set Analysis expressions, however, modify those selections. This change should always be clearly signposted to users with labels and legends. For things like prior period comparison, Set Analysis is brilliant.
Caution should be taken though not to confuse users. A common use case for Set Analysis is to tie a chart or an expression to a specific dimension value. This overrides the users selection. Another operator that can be used is Star Equals, this gives the intersection of what is specified in the Set and the users selection.
You will note that Equals and Star Equals give identical results above. Look what happens though when we select a single Region:. With Equals the users selection is totally ignored, but with Star Equals it is respected.
For prior period comparison it may be that you want to ignore the users selection, so if they select only you still want to show as the comparative period. Generally though I think the Star Equals is the better approach. A similar approach can be applied on both sides of a comparison expression. This shows there are options around how you apply set analysis to your expressions, and you need to think carefully about what you apply.
Even in this simple example with a single field to be selected there is much to consider. The main reason, I think, that so few developers that I speak to know about this function is due to a bug in QlikView.
For many versions the use of Star Equals gave a wiggly red line, indicating bad syntax. This would put off all but the most confident developer! The syntax highlighting however is only a guide, and sometimes you just have to push on and ignore it. It does make the rest of the development of that expression more tricky, but there you go.
Once you start using the correct expression syntax then many of the issues of apps being counter intuitive can go away, and users can feel there selections are not being ignored. There are many great resources for this already.
As well as covering all operators and modifiers including the slightly newer P and E functions it has a set of handy Venn Diagrams showing what is included when any operator is used. A quick Google search will also turn up a lot of results, including many on Qlik Community.
Using this example you can see exactly what happens to each of the expressions when you apply different selections. You have added to the field of knowledge in set analysis which is no small feat.
Thanks Aaron. I picked up a few tips from your Sense Master Items post today also — so we are quits! Thanks Donnie.Filters let you refine your data. You can add filters to tasks, reports, objects, and users. They can be static or dynamic. Static filters are selected from a list of QlikView or Qlik Sense field values. You must manually edit the filter to change values. Dynamic filters use QlikView or Qlik Sense expressions and searches. Expressions must use QlikView or Qlik Sense rules and syntax, and return a single value.
Expressions are evaluated, and the results are used to filter data. Searches leverage the various kinds of search available in QlikView or Qlik Sense. You do not have to modify dynamic filters to gain new values. Filters are verified before generating each report. Task, user, and report filters are checked against document data. If they are invalid, you will see an error stating that filters cannot be applied on the Task executions page. This happens before the calculation of report conditions, and before report generation.
Qlik NPrinting will resolve filters by applying them in a sequence where the initial state has no selections on any field. Qlik NPrinting filter rules provide predictable behavior with a limited number of options:.
There is one specific flag that allows you to change these behaviors: Override values. This flag, which is related to specific fields of a filter, allows you to clear the selections on a certain field on a certain level of the chain.
You can also add other values instead. These examples show filters assigned to specific entities for different values of the same field, with Override values selected or de-selected:. If the Fully cached notification appears on the right, it indicates that the Qlik NPrinting cache contains all values of a field. This means that when you start typing in the Values dialog box, Qlik NPrinting searches all the values that match your string.
The Qlik NPrinting cache only stores the first values from QlikView fields with a large amount of distinct values. In this case, the Fully cached green label does not appear. You will instead see a Search button on the right side of the value field. Type the string, or part of the string, that you want to find. When there is a large amount of distinct values, you have to manually insert the whole value that you want to filter.
You can choose to Override values : if you select this check box, all selections applied by other filters on the same field will be removed and replaced by the values of the field set in current filter. If Override values is not selected, a new filter will be created by the intersection of field values from different filters applied on the selected field. When you create a filter, you can exclude specific values, using the Select excluded check box.
For example, you may want to exclude data from certain years, or geographic regions. For example, if the field was Countryyou could exclude Sweden and Italy. You can use QlikView and Qlik Sense expressions to calculate values to be used in filters. Each expression must return a single value, and use QlikView or Qlik Sense syntax.The technique discussed in this post applies to both QlikView and Qlik Sense.
The screenshots shown are from QlikView. Some of the visuals are a bit different in Qlik Sense, but the idea and expressions demonstrated are the same.
A downloadable example to accompany this post is available here. To limit the selection to Customer, add an Aggr function to the expression:. Only the Customer field is listed in the Aggr dimension, so selections will be made only in Customer. We can fix that with a bit of Set Analysis. Clicking Michelle in the chart correctly selects her name as SalesPerson, but makes unexpected selections in HireDate and SalesManager.
We can widen selections as well.
This listbox will make selections in Customer, Country, SalesPerson and Year, even though only Customer is displayed in the listbox. Returning to our first example, we might create a new field in the script as:. I want selections to be reflected in the OrderID field. Use only when required and avoid using or benchmark the impact in large applications.
Calculated Dimensions can also be a source of slow performance, precalculate fields in the script when possible. For your initial listbox with Customer and Country I would use the listbox expression to show Country.
No Aggr, no Calculated Dimension or extra combined fields. I did manage to download it from the Recipes page. Interesting use of Aggr. In Qlik Sense, the whole text shows before Hovering Over.
Is there a way to for this not to happen? There may be an extension available on branch. Your email address will not be published. Notify me of follow-up comments by email.
Subscribe to RSS
Notify me of new posts by email. Hi Gysbert, Thanks for the feedback. Link corrected. Leave a Reply Cancel reply Your email address will not be published.
Recipes for Qlikview Success.Sometimes you need to approach your data from a different direction. Normally when making selections to filter data in a QlikView application, you click specific values in some field. The example below shows selecting OrderID values and the result. They must also be able to search for customers having total sales over a threshold amount.
QlikView supports searching in a field using an expression that aggregates some other field or compares fields. In the example below, the user has activated the OrderID search box and typed an expression to find orders whose total sales amount is over 10, After the user types the search string and presses Enter, the only orders shown are those whose detail rows total to more than 10, QlikView is not filtering for order detail lines with amounts greater than this threshold; rather, it is filtering on the total amount aggregated over each OrderID.
Use this method to perform ad hoc searches, or to test your search logic as a developer before automating it. That leads us to the next step. Automating this kind of search gives less experienced users the same power and flexibility to analyze their data. In the example below, an action button lets the user perform an indirect search with one click. The button uses the Select in Field action, whose two parameters are a field to search and the search string to use. Notice the syntax of the Search String parameter.
This is a simple automation example where the search value is hard-coded. Other options include placing the search amount in a variable tied to a slider object or input box, saving the search as a bookmark, or adding the action to a gauge chart or other object rather than a button. QlikView provides several ways to search for data based on associated values in the application data model. Set analysis and chart dimension limits are two other methods for implementing this kind of search.
To thrive with your data, your people, processes, and technology must all be data-focused. This may sound daunting, but we can help you get there. Sign up to meet with one of our analytics experts who will review your data struggles and help map out steps to achieve data-driven decision making.
Blog Home. Step 1: Search Based on an Expression QlikView supports searching in a field using an expression that aggregates some other field or compares fields. Omit the equal sign and you likely will find no matches. You must thoroughly understand the data model in order to structure this kind of search correctly.
Conclusion QlikView provides several ways to search for data based on associated values in the application data model. Analytics in Action. Industry Insights. Successfully subscribed to the newsletter. Field have an error. Please check and try again. Contact Us Thanks for your inquiry! Someone will be in touch shortly.Are there any scenarios you cannot cover with this wizard? Please tell me that and drop me a line! Please post any questions, bugs and especially ideas how to improve the "Set Analysis Wizard for QlikView" following this link.
In the form below you can select an action and define the required parameters for the selected action. After clicking on "Save" the action will be added to the Set Analysis expression.
You are welcome to share the Set Analysis Expression with others by using the following link. Just send this link to others, post it in discussions forums, etc. Help Debug Output Reset Wizard. Define the set First define the basis expression you would like to calculate. Hover the Inline hints: Yes, you'll see hints like this one when hovering those icons Based on the current selection all values everything that the current selection excludes the previous selection the next selection the 2nd next selection the 3rd next selection the 2nd previous selection the 3rd previous selection the bookmark with a given Id the current selection and the bookmark with the given Id the current selection except those records belonging to the bookmark with a given Id the intersection between the current selection and the bookmark with a given Id Select the basis for your set.
Have a look at the QlikView Reference manual for further information! Note: If the field name contains spaces just enter the field name as it is, the necessary brackets [ and ] will be added automatically.
Add Personal Comment. Personal Comment: hide. Modify the set Add an action to modify the set defined above. No actions defined so far Reset Wizard.
The dark mode beta is finally here. Change your preferences any time. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. The set identifier of 1 represents "the full set of all the records in the application, irrespective of any selections made. For more information, you can read this help section from the Qlik site.
Learn more. Asked 2 years, 6 months ago. Active 2 years, 5 months ago. Viewed 2k times. Joeysonic Joeysonic 9 9 bronze badges. Active Oldest Votes. I have tried this with the dollar sign. Antoine Stas Antoine Stas 1 1 silver badge 11 11 bronze badges. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name.
Email Required, but never shown. The Overflow Blog. Podcast Cryptocurrency-Based Life Forms. Q2 Community Roadmap. Featured on Meta. Community and Moderator guidelines for escalating issues via new response…. Feedback on Q2 Community Roadmap. Triage needs to be fixed urgently, and users need to be notified upon….