In Set expressions, an equals sign can be used to assign a selection to a field. But this is not the only assignment symbol that can be used. There are a couple of others. This post is about the alternative assignments symbols that can be used, and their respective use cases.Strictly speaking, the equals sign in set expressions is not an equals sign. Rather, it is an assignment symbol. This is the reason why you cannot use relational operators in its place. The equals sign assigns a selection state to a field e.g. { $ <Country = {Sweden, Germany, 'United States'}> }In this case, the element set ‘Sweden’, ‘Germany’, ‘United States’ is assigned as selection to the field “Country”.But what if the set identifier already has a selection in the field “Country”?In such a case, the old selection will be replaced by the new one. It will be like first clearing the old selection of the field, then applying a new selection.However, this is not the only way to assign a set as selection in a field. You can also use assignments with implicit set operators. These will use the existing selection in the field to define a new selection:Implicit Union: { $ <Country += {'United States'}> }Note the plus sign.This expression will use the union between the existing selection and ‘United States’ as new selection in the field, i.e. add the listed values to the previously select ones. The use case is not a common one, but it happens sometimes that you always want to show a specific country (or product or customer) as a comparison to the existing selection. Then the implicit union can be used.Implicit Intersection: { $ <Country *= {"=Continent='Europe' "}> }Note the asterisk.This will use the intersection between the existing selection of countries and the countries in Europe as new selection in the field. (The search is an expression search that picks out European countries.) The set expression will not remove any part of the existing condition – instead it will just be an additional limitation.This construction can in many cases be used instead of a normal assignment. In fact, it is often a much better solution than the standard assignment, since it respects the user selection and thus is easier to understand.The implicit intersection should be used more.Implicit Exclusion: { $ <Country -= {'United States'}> }Note the minus sign.This expression will use the existing selection, but exclude ‘United States’. The use case for an implicit exclusion is exactly this – you want to exclude a value. Hence, this construction is very useful.Implicit Symmetric Difference: { $ <Country /= {'United States'}> }Note the slash.The above expression will select values that belong either to existing selection, or to the values in the set expression, but not to both. It’s like an XOR. I have never used this, and I would like to challenge the readers to come up with a relevant use case.Bottom line: Assignments with implicit set operators are sometimes very useful. You should definitely consider using the implicit intersection and the implicit exclusion.HICFurther reading related to this topic:A Primer on Set AnalysisWhy is it called Set Analysis?
...View More
I ran into an interesting dilemma last week when trying to create a measure for a KPI object in Qlik Sense. Let me set the scene. I was working on an app that tracks organization members and activities. The requirement was to look at the number of members in the organization over time and identify the largest and lowest monthly gain and loss. I was able to build the table below to see how the number of members varied over time and where the largest gain and loss were. The largest gain was from Nov 2015 to Dec 2015 with a 15.91% member increase. The largest loss was Sept 2015 to Oct 2015 with -48.88% member decrease.The measures for this table looked like this:Number of Members – count the number of distinct membersDifference – count the difference between this month and the previous month using the Above() function to capture the previous month’s number of membersVariance – show the difference as a percentageNow here is where my dilemma began. I needed to capture the percentage and number of the gains and losses in a KPI object and I was not sure how to do that. I knew there had to be a way to do it in Qlik Sense so I turned to my colleague Arturo Munoz who enlightened me to the fact that when I use the Aggr() function, it creates a temporary staged result set, like a table, similar to what I was doing in the table above. So I tried this out in a KPI object using the Variance expressions above and aggregating the results by MonthYear. Since I wanted the largest gain and loss, I also used the Max() and Min() functions. Here is what my KPI measures looked like when I was done:Largest Gain %Largest Loss %I was able to use the Above() function in my KPI measure to get the variance and then I aggregated the result by MonthYear and captured the maximum and minimum variance. I was able to apply this to my app for other KPIs as well. It was brilliant. I never thought that I could use the Above() function outside of a Table object but I was very happy to find out I was wrong. In order for the Aggr() function to work as I expected, I needed to make sure the MonthYear field was sorted properly in ascending order so I handled this in my script. Note that if the MonthYear field was not in ascending order, the Above() function may not always return the previous month thus returning the wrong results.Henric Cronstrom has blogged about the Aggr() function in his blog Aggregations and Function Classes as has Adam Bellerby in his AGGR... blog. The Aggr() function can be quite helpful and while I have gotten use to using it, I realized that there are still so many other ways it can be used. I hope you find this way of using it as helpful as I did.Thanks,Jennell
...View More
Facts in BI solutions are usually additive – but not always. To avoid mistakes, it is important to understand when you can sum the number, and when you cannot. This post will not only help you understand the problem, but also point at some possible ways to handle non-additive numbers.
For all of my large projects I use angular to handle the pages, content, variables, navigation etc. I have setup a template that everyone can access and download from git and branch.qlik.com. In this tutorial, I will show you how to install and use it.Go to Git or Branch and download the project on your server or if you do not have one, in your desktop Qlik\Sense\Extensions.Assuming that you are running git and you are familiar with the console, type bower install to get all of the necessary libraries.Config files below to point to your pathsIndex.html
<link rel="stylesheet" href="http://localhost:4848/resources/autogenerated/qlikui.css">
<link rel="stylesheet" href="http://localhost:4848/resources/assets/client/client.css" media="all">
<script src="http://localhost:4848/resources/assets/external/requirejs/require.js" data-main="js/lib/main.js"></script>
js/lib/main.jspath to local script and libraries, L1
var scriptsUrl = 'http://localhost:4848/extensions/angularTemplate/';
path to Sense scripts like "js/qlik", L4
baseUrl: "http://localhost:4848/resources",
Now lets go over the anatomy of the template.Index.html is our container. It has the navigation and 2 controllers, header and main.The header is for the any persistent toolbar you may want to use, plus it holds the global bindings like navigation and clearAll() for our sense object selections. The next one is the current page and holds all the objects specific to this page.For this tutorial I used the helpdesk that is bundled with any Sense installation. In this template, I have two ways of displaying Sense objects. One is by getting the object as is from the app and the other one is by calling a HyperQube and displaying the desired results. For the first, we need to put the object ids into an array that can be accessed from our html template.In js/controllers/dashboard.js we define our variable with the objects as (L23)
$scope.objects = ['a5e0f12c-38f5-4da9-8f3f-0e4566b28398'];
and in view/dashboard.html we display it with a height of 300px as :<get-object object="objects[0]" height="300"></get-object>The other way is a little more complicated since it requires a service, api.getHyperCube. In this, we can pass an array of dimensions, measures, or both and a limit, if we want to limit our results.js/controllers/dashboard.html
me.measures = [
["Count( {$<Priority={'High'}, Status -={'Closed'} >} Distinct %CaseId )", false],
["Count( {$<Priority={'Medium'}, Status -={'Closed'} >} Distinct %CaseId )", false],
["Count( {$<Priority={'Low'}, Status -={'Closed'} >} Distinct %CaseId )", false],
];
$scope.kapi = [];
angular.forEach(me.measures, function(value, key) {
api.getHyperCube([], [value[0]], function(data){
$scope.kapi[key] = (value[1])?utility.string2thousands(data[0][0].qText):data[0][0].qText;
});
});
and in the view/dashboard.html<h2>{{ kapi[2] }}</h2>I am also using a utility to display numbers as per Sense KPI format.Now, say that you want to add another page named "performance" as the Sense sheet, you will need to define it in js/lib/main.js.Copy and rename js/controllers/dashboard.js to performance.jsCopy and rename views/dashboard.html to performance.htmlAdd in L5 paths your new controller path, "controller.performance': scriptsUrl + 'js/controllers/performance'".In L35 add your new route (state)
.state('dashboard', {
url: "/dashboard",
views: {
'header': {
templateUrl: "views/header.html",
controller: 'controller.header'
},
'main': {
templateUrl: "views/performance.html",
controller: 'controller.performance'
},
}
})
In L49 'Require' add the controller again 'controller.dashboard',And make sure your controller is defined properly, L10 of js/controllers/performance.js
app.obj.angularApp
.controller('controller.performance', function ($scope, $rootScope, $location, $injector, api, utility) {
var me = {};
me.init = function () {
$rootScope.page = 2;
$rootScope.title = 'Performance';
Also, we do not need the measures for this one so delete L17 and $scope.kapiFor this tutorial we will only add the Resurce Details table. So change the object id to 'uETyGUP' and remove from views/performance.html all the kpisLast, add the in the index.html the navigation in L39 <li ng-class="(page==2) ? 'active' : ''"><a ng-click="goTo('performance')">Performance</a></li>Your final webpage should look like this:For any further explanation and feature requests please let me know.Coming up: setting a similar template for the Engine API and qSocks.Git: https://github.com/yianni-ververis/capabilities-api-angular-templateQlik Branch: http://branch.qlik.com/#/project/56b4a40140a985c431a64b08Yianni
...View More
During the last days we have been working in a new visualization piece that will be live soon in The Telegraph website. We got some data with daily refugees arrivals to different countries, the goal was to visualize how many people have moved across Europe during the last few months in a map, but we also wanted to plot a line/area chart with the cumulative number of arrivals per country and per day so you can see trends and evolution over time. Our data set included daily data but it didn't contain cumulative numbers so we had to calculate them.The calculation should be something like this:If is the first row or country is different than previous row country, then load the field value, else load the field value plus same field previous row value.Step 1 Data LoadStart by loading our data table with the daily arrival values by country.
//Load the main table
DataLoad:
LOAD
"Date",
Country,
"Daily Estimated Arrivals"
FROM [lib://LibraryName/Data.qvd] (qvd);
The generated table will look like the picture below:As you can notice the data is not sorted in a way that will let me successfully calculate the new field as I designed it because it will mix data from different countries, so my next move will be to sort the table by country and date.Step 2 Sorting the table
//Sorting the table
SortLoad:
NoConcatenate Load
"Date",
Country,
"Daily Estimated Arrivals"
Resident DataLoad
Order by Country, Date;
Drop table DataLoad; //Deletes the previous table no longer needed
Because I’m using a Resident Load statement to load and sort the values from the previous table, and both tables have identical field sets, I need to specify NoConcatenate before the load, otherwise both tables will be automatically concatenated. Using ‘Order By’ clause will sort the table by Country and then by Date. Finally, because the first table DataLoad will be no longer needed I’ll delete it using Drop Table.My new table will be as in the picture belowStep 3 Adding the calculated new fieldNow that we have the data table ready we can finally calculate the new field to the table containing the cumulative daily estimated arrivals. To do so we will use the peek and previous script functions. Both functions are similar but they have some key differences that you should learn, please don’t miss this blog post: Peek vs Previous: when to use each
//Add cumulative data to the table
CummulativeLoad:
NoConcatenate Load
"Date",
Country,
"Daily Estimated Arrivals",
If(RowNo()=1 Or Country<>Previous(Country),
"Daily Estimated Arrivals",
"Daily Estimated Arrivals" + Peek("Cumulative Daily Estimated Arrivals", RowNo()-2))
as "Cumulative Daily Estimated Arrivals"
Resident SortLoad;
Drop table SortLoad; //Deletes the previous table no longer needed
Click on the image below to see how we created the new field to match with our calculation statement.The final result will be a data table containing the fields to support both daily and cumulative charts.Enjoy Qliking!AMZ(Note: I divided this example in 3 steps so hopefully it is clearer, but step 2 and 3 can be merged in one single step)
...View More
Recently, I put together three simple templates for use in the dev hub. Check out where to find them and more below.Blank Bootstrap TemplateThe "Blank Bootstrap Template" download and installation instructions can be found at Qlik Branch - Blank Bootstrap Template. This template is simply a blank template, with boilerplate html, css, and javascript for creating a webapp or mashup using the Qlik Capability APIs. The template loads Bootstrap for you, and takes care of some conflicting css issues between bootstrap and the css used for the Capability API. If you'd like to build your own web app or mashup and use bootstrap, this is a good place to start.Bootstrap Tabs TemplateThe "Bootstrap Tabs Template" download and installation instructions can be found at Qlik Branch - Bootstrap Tabs Template. This template builds on the blank bootstrap template, and includes bootstrap tabs and qvplaceholders so you can easily drag and drop objects and create a mashup or web app from an existing Qlik Sense app within minutes. A demo can be found here, and a video demo is below. Video Link : 3972 Bootstrap Off-canvas Menu TemplateThe "Bootstrap off-canvas menu template" download and installation instructions can be found at Qlik Branch - Bootstrap off-canvas menu template. This template also builds on the blank bootstrap template, and includes a very simple off-canvas menu so you can stash a filter pane or anything else you'd like in an off-canvas menu, as well as qvplaceholders, allowing you to quickly create a web app or mashup from an existing Qlik Sense app within minutes. A demo can be found here.
...View More
Have you every used the Autogenerate clause in your load statement? The Autogenerate clause is used in the load statement to automatically generate data in Qlik Sense or QlikView. In the load statement you may see Autogenerate replace the From or Resident clause. The key difference with the Autogenerate clause noted in Qlik Sense Help is that:“The field list must not contain expressions which require data from an external data source or a previously loaded table, unless you refer to a single field value in a previously loaded table with the Peek function.”I have seen the Autogenerate clause used to create a table of random numbers or to create a table with a range of dates. Let’s take a look at how we can use it in the script. Let’s assume I need a table of random numbers in my data model. I can create a file, such as an Excel file, that has all the random numbers I need and load that into Qlik Sense or I can save myself some time and let Qlik Sense create the table for me. In the script below, I am loading a table with an ID and a random number between 0 and 1 using the Autogenerate clause. The 100 in parentheses lets Qlik Sense know how many records I would like to generate. Note that the parentheses after Autogenerate are optional.The resulting table looks like this:That was pretty simple. Now let’s see how we can generate a table with a range of dates. If we want a table with all the dates in 2016, we can use a script (as seen below) to generate each date for the year. The Autogenerate clause creates one date every time it reads the script until it gets to the end of the year (12/31/2016 in this example).To begin the script, the variables for the date range are set. The variable vMinDate stores the first day in the year and vMaxDate stores the last day in the year. In the Load script, the TempDate is created. If the first row is being created, then the date should be the value of vMinDate (1/1/2016). If it is not the first row then increment the date by one day using the RowNo() function. The While clause will repeat the script until the last day of the year is reached. The resulting table looks like the image below with all dates from 1/1/2016 to 12/31/2016:The Autogenerate clause in the load script provides an easy way to generate data in your Qlik Sense or QlikView data model. You can control how many records you would like to add and, as done in the date example, you can repeat the script based on some condition. So the next time you need to generate records that are not using an external data source or previously loaded table, try out the Autogenerate clause in your script.Thanks,Jennell
...View More
In my previous posts I have showed how to use bootstrap's drop-down menus with the Capabilities API. In this tutorial I will show you how to use jQuery's UI to get results as you type like thisFrom the dev-hub (http://localhost:4848/dev-hub/) click on "Create New", to create a new mashup. Give it a name and select as template the "Grid mashup template"Select the "Helpdesk Management.qvf" for your app.On the right hand side, click on "Create a list". This will be the results that we would like to search on.From the left hand side click and drag the "case details" table onto the preview panelClick on "Fields" and select "Case Owner".Under "Rows" put 100 since we do not need any more for this tutorial, and give a name to your callback function. I usually name mine as "refactorData" because I restructure all of the data in a more accessible format. Once you hit create, you will see that the code for the list was added as well as the refactorData in line 108.In our html's headers, after bootstrap css and right before requirejs, add the jquery ui css and js
<link rel="stylesheet" href="https://community.qlik.com//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
<script src="https://code.jquery.com/jquery-2.1.4.min.js"></script>
<script src="https://code.jquery.com/ui/1.11.4/jquery-ui.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/js/bootstrap.min.js"></script>
Let's add the code into our callback function
function refactorData(reply, app){
var data = [];
for (var i = 0; i < reply.qListObject.qDataPages[0].qMatrix.length; i++) {
data.push(reply.qListObject.qDataPages[0].qMatrix[0].qText);
}
$("#search").autocomplete({
source: data
});
};
In our "function AppUi ( app ) {" add the code below that will make the actual selection in our app
$( "#searchBtn" ).on( 'click', function () {
app.field('Case Owner').selectValues([$('#search').val()], false, false);
});
Now Let's add the text box in our html page. Replace the "<div class="col-sm-4 qvplaceholder" id="QV01">" with this one
<div class="col-sm-4 qvplaceholder" id="QV01">
<div class="input-group input-group-sm">
<input type="text" class="form-control" placeholder="Type in the Case Owner" aria-describedby="basic-addon1" id="search" name="search">
<span class="input-group-btn">
<button class="btn btn-default" type="button" id="searchBtn"><span class="glyphicon glyphicon-search" aria-hidden="true"></span> Search</button>
</span>
</div>
</div>
And that's it. The final should look like thisYV
...View More
Hello Qlik Community, in this post I have the pleasure of introducing Marcus Spitzmiller. Marcus is a member of the Qlik Enterprise Architecture team focusing on enterprise deployments and best practices. His areas of expertise include scalability and performance, deployment best practices, integration, and security. Marcus has been with Qlik for 6.5 years. In this post he will introduce you to Qlik Sense Stream management, covering security rules and exception management. Managing Qlik Sense StreamsAt the center of Qlik Sense’s security is an attribute based access control component called the Security Rules Engine. Qlk’s Product Manager for security, Fredrik Lautrup, ( flp ) does a great job of explaining just what that means here (https://community.qlik.com/blogs/qlikviewdesignblog/2015/03/10/why-security-rules-in-qlik-sense).Administrators of Qlik Sense can leverage attributes about users, applications, streams, data connections and much more to govern user authorization (that is, who can do what) via the Security Rules Engine.Qlik’s Michael Tarallo (@mto) has produced a number of great videos that describe the Qlik Management Console and the functions available within it here (https://community.qlik.com/docs/DOC-7144), and I would encourage you to review those videos in the “Management Console (QMC) Series” if you don’t yet have an understanding of concepts like Streams, Custom Properties, and User Directory Connectors.In this video I show how you can effectively use the power of the Security Rules Engine to manage multiple groups of users, multiple streams, and do so with as little administrative maintenance as possible. Be on the lookout for the following best practices leveraged within this video:- Build “Many to Many” Security rules to enable one stream security rule to govern many streams and many users at once.- Use Custom Properties to avoid hard coding of values into Security Rules.- Avoid changing the out of the box rules. If you need to change a rule’s default behavior, disable the rule and make a copy of it.- Finally, manage collections of things before you manage the thing itself. With the concepts I detail in this video, you can manage many streams, many groups, (and by extension many data connections, and more) with little administrative overhead, and manage individual things (like applications) as an exception.The Security Rules Engine is a tremendously powerful component of the Qlik Sense architecture, and your deployment requires planning. As a general guideline, if you find yourself thinking “there has got to be a better way”, there probably is! That is your cue to reach out to the many Qlik resources you have available to you through QlikCommunity, Qlik Education, Qlik Partners, Qlik Consulting, and Qlik Sales teams.Enjoy the video!Marcus
...View More
By today an ordinary human being would had been exposed to probably dozens of lists about several and arbitrary topics. For sure we all have read classics lists such as the best movies of 2015, the 25 books of the year, or top 10 songs of 2015, but on the internet, we can go much further than that and even have a list of lists.I don’t want this year to end without sharing with you a list of the best posts of the year. But let me give you some numbers first.This year 2015, 10 authors wrote 72 posts (not including this one) and a total of 36.756 words were written. We wrote the word "Qlik" 323 times during 2015 making an impressive average of 4.5 times per post! You, on the other hand, participated by commenting 619 times (8.6 comments per post of average), thanks a lot for that, we really appreciate your comments.Most popular posts in 2015Most read/visited postsA Primer on Set Analysis. Set analysis, the most powerful and sometimes complex thing in Qlik, in this post, HIC brilliantly explains what Set Analysis is and walks us through it with simple examples.Using Qlik Sense Analytics to Get in Shape. Jason Yeung reveals how he used Qlik Sense to track and better understand his fitness activity.Qlik Sense – Date & Time. Jennell describes what is and how to work with the new Date and Time fields in Qlik Sense.The search string. QlikView search capabilities illustrated with detail, I bet you didn't know a bunch of the available search modifiers.How to embed a Qlik Sense Chart on your website. When I wrote this post it was the best way to share a chart, now it's still a good way to share it internally within your company or integrate it in a web page, but since the introduction of Qlik Cloud, we have better sharing options available.Most commented posts of the yearSomething interesting is going on in these posts.The search string.A Myth about the Number of Hops. HIC tears down a false myth once again, really interesting lecture.Excluding values in Set Analysis. Including values in Set Analysis conditions is easy but what about excluding values?Evaluate() Function. Discover what's Evaluate(), how to use it, explore examples, and more.The As-Of Table. Are you trapped in calculating rolling averages and other accumulations? Then, don't miss this post.Most liked and bookmarked contentBoth strongly correlated.A Primer on Set Analysis. The As-Of Table.The search string.Dates in Set Analysis. Yet another post on Set Analysis, this time, HIC gets inside out with dates.Excluding values in Set Analysis.The underdogs 2015I can't finish the last post of the year without a selection of not-so-popular posts (yet), that deserve some more attention.Mode Function. Do you know what Mode() does? well, it's simple but might be an interesting addition for your next project.Qlik Sense Search Cheat Sheet Some of the information needs we face every day don't require complex expressions or customized charts, sometimes a good use of search can help you to visualize what you was looking for.Qlik Dev Hub replaces Qlik Sense Workbench in Qlik Sense 2.1 Maybe a small step but a very important one in Developers' world.Mashup Editor - Toggle charts with jquery Do you miss the good old QlikView container object? Well, Yiannis have created a tutorial to bring it back to life in Qlik Sense.Qlik Sense SAML - A standardized approach to authentication Learn what SAML is and how to implement it with Qlik SenseHope you all have a great end of the year!AMZ
...View More
.png "Former Employee"){kind=icon}
