For Qlik Sense 2.1, the Qlik Dev Hub, accessible at https://<ServerName>/dev-hub/ , replaces the Qlik Sense Workbench, formerly accessed by https://<ServerName>/workbench/ (this will now redirect you to the Dev Hub). In addition to a brand new user interface layout, the Dev Hub has a couple of new features worth mentioning.The new user interface has a sleek dark color design, and its layout more closely resembles the Qlik Sense client than its predecessor. The top nav bar has a drop-down menu that provides access to an “About” link, which displays information regarding the Qlik Dev Hub, and a “Help” link, which brings up helpful documentation in a new browser window. There’s an added search functionality also included on the top nav bar which lets you search through your mashups and extensions, which is really useful as your number of them grows. A second nav bar allows filter by “Mashups” or “Visualization extensions.” There is also a “Create new” button that lets you jump right into creating a mashup or extension.A left-aligned nav bar allows you to choose from “Single configurator,” “Extension editor,” “Mashup editor,” or “Engine API Explorer.” The “Single Configurator” was previously not accessible from the Qlik Sense Workbench, and its inclusion in the Qlik Dev Hub is very convenient. The “Engine API Explorer” was previously known as the “Protocol tester” and has a new look to match the Dev Hub.The “Mashup editor” has a couple new features. There are now menus on the left and right of the main work area which can be toggled to make the main work area larger. An added feature I’m very fond of is the ability to create new files for your mashup right from the top nav bar. The “Preview” tab has been improved, and you can now interact with your mashup in addition to dragging and dropping charts, which is a nice improvement because formerly if you had off-canvas chart areas, there was no way to access them to drag and drop charts.The Qlik Dev Hub comes with quite a few new templates for both mashups and visualization extensions. I highly suggest you try some of these out, I think you’ll be amazed at how quickly you can put together a decent mashup, even with little to no web development skills. Steps below -1) Navigate to the Qlik Dev Hub, click on the “Create new” button, give your mashup a name, and select either “Basic single page mashup” or “Slideshow mashup” from the dropdown menu.2) Click the “Create and edit” button and this will open up your mashup in the “Mashup editor” for you.3) Select an app from the dropdown menu in the left nav, and start dragging and dropping charts into the chart areas.4) Customize as much as you want by modifying the auto-generated HTML and JS filesI’m going to include a few links to documentation below. If you haven’t tried the new Qlik Dev Hub yet, you should definitely check it out.Dev Hub at a glanceMashup EditorGetting started building mashups with Qlik Dev Hub ...View More

Almost every person I meet to talk about Qlik products and security bring up the concept of section access for discussion. I think section access is one of those things that you either love or hate, but as a company using Qlik products you can’t live without it. The great benefit of section access, in my view, is that it’s driven by the data model which makes it really powerful.It would be great to get your comments on what you think are the strengths of section access.As section access is a critical part of how we protect data, we carried over its capabilities from QlikView to Qlik Sense and adapted it to Qlik Sense architecture.So what has changed?In Qlik Sense the section access is different in that the names of the columns available have changed:ColumnDescriptionACCESSCan be USER or ADMIN. The ADMIN access was introduced in Qlik Sense 2.0 and gives the user full access to data.USERIDThe name of the user in the format of [User Directory]\[User ID]GROUPValue of the attribute group on a user[REDUCTION]Is the field on which the reduction is performedOMITFields that should not be available to the GROUP or USERIDIn Qlik Sense, a script for section access could look like the following:section access;load * inline [ACCESS, USERID, REDUCTION, OMITUSER, QVNCYCLES\flp, 1, RegionUSER, QVNCYCLES\kag, 2,];The example above would give the user QVNCYCLES\flp access to rows with a one in the field called REDUCTION without getting access to data in the Region field, and QVNCYCLES\kag would see the data with a two in the REDUCTION field.In Qlik Sense section access is applied using strict exclusion, which means that if you are not explicitly granted access you will not be allowed to see any data.My favourite improvement in section access for Qlik Sense is that it will be harder to lock yourself out of an app. In Qlik Sense you have the option to open an app without data. This means that if you have permissions to change the script you can open the app without data even if you don’t have access to any. This will allow you to change the section access part of the script instead of being locked out.We have also introduced the capabilities to use attributes sent in at the time of the user authentication to be used with section access. This means that we now can base what data you get access to using the group attribute that can be inserted using SAML or tickets. I hope that you found these tips on Section Access for Qlik Sense helpful. If you have questions on this blog post or have ideas of what you want to read about in the future, please don’t hesitate to add comments to post ...View More

One of the great features in Qlik Sense is the Smart Search capabilities. Smart Search allows you to search the data in your app when you are on a sheet. All you have to do is select the Smart Search icon on the selection bar and type what you want to search for. In the image below I start typing “fresh vegetables” and data that matches “fresh” or “ve” (the start of the word “vegetables”) is displayed. From here, I can select one of the items found if it is what I am looking for. I will select “Fresh Vegetables” in the Product Sub Group Desc field and this selection will be added to the selection bar.One thing I should note is that I did not use quotes when I searched for “Fresh Vegetables” so “fresh” and “vegetables” were interpreted as separate search terms. If I use quotes, my search returns less results (seen in the image below) because my search term was more specific and considered one search term versus two.Now if I want to perform another search, I can do so and it will automatically search within my selections which is Fresh Vegetables. If I search for broccoli, all possible data will be displayed within my Fresh Vegetables selection.Now say I search for something that is not within my Fresh Vegetable selection like apple. I will be given a message indicating that no matches were found and will be prompted to start a new search for only apple if I chose. If I select the “Start a new search …” button, then my Fresh Vegetables selection will be de-selected before the search begins.By default, Smart Search searches all fields in the data model but you have the option to set what fields you would like to include or exclude in the Smart Search. This can be done in the script by using the Search Include or Search Exclude statements:Search Include * fieldlistSearch Exclude * fieldlistFieldlist is a comma separated list of the fields that should be included or excluded in the search. The Search Include statement is used to indicate which field(s) should be searched when performing a search. In some apps, there may be several fields that the user may not need to search. In that case, it is smart to use the Search Include to narrow down the list of fields to search. This can help with the performance of the search as well. The Search Exclude statement is used to indicate which field(s) should not be used in the search. This may be used to exclude ID or key fields that were used to build the data model but that the user does not need to search. In both statements, wildcard characters * and ? can be used. Just to show you how this works, if I add the Search Exclude statement below to my script and reload, it will exclude all values in the “Line Desc 1” field when a search is being performed.In the Master Items, the “Line Desc 1” field is added as a Dimension named Product. When I performed the search earlier, broccoli results were found in the Product dimension. Now watch what happens when I do another search for Broccoli within my Fresh Vegetables selection. Broccoli does not come up in my search results because the exclude statement does not allow a search in the “Line Desc 1” field/Product dimension.Smart Search provides an easy way for users to find the data they are looking for to filter their data. The Search Include and Search Exclude statements offer the developer a way to control the fields that can be searched. This can improve performance and it can make it easier for the user to focus on the fields that are most relevant. Keep these statements in mind the next time you are developing an app. It will improve the users search experience.Thanks,Jennell ...View More

Hello Qlik Community! - I am pleased to announce the availability of Qlik Sense 2.1. Our third and final release in the Qlik Sense product family this year. With this release, Qlik is continuing its commitment to helping organizations use analytics to see the whole story that lives within their data. Version 2.1 provides the most complete Qlik Sense experience to date and allows customers to explore, create and collaborate with greater simplicity by taking advantage of Qlik’s unique associative model.Allow me to summarize some of the new feature highlights in this release:Individuals will benefit from a new visual exploration capability that allows users to explore analytics by changing properties, such as sorting and coloring, without having to enter edit mode or directly change the underlying contentGroups will benefit from new storytelling features that allow data stories to be directly published to PowerPoint presentations to facilitate sharing and collaborationOrganizations can take advantage of new variables that empower content creators and developers to build more sophisticated analyticsDevelopers will be able empowered with a new development hub to more easily take full advantage of the Qlik Analytics Platform for web mashups, custom apps, and extensionsThere is of course much more including additional user experience features and even new Qlik DataMarket Topical Packages. These premium subscription packages include 180 currencies and weather data for 2600 cities. Now....enough reading and get to watching! The below New Feature video summarizes these highlights and then will link you to other videos to demonstrate and briefly show you how to use these new features.Note:Qlik Sense Desktop is available right now http://www.qlik.com/us/explore/products/sense/desktopQlik Sense Enterprise Server and other associated files for our customers and partners will be available via on our download site (customer and partner access only) later today.You can experience these features immediately by visiting and registering on the Qlik Sense Cloud.Regards,Michael TaralloSenior Product Marketing ManagerQlik@mtaralloQlik Sense 2.1 New Features PresentationOther videos worth watching:Qlik Sense - Visual Exploration (video)Qlik Sense - Variables Interface (video)Qlik Sense - Export to PowerPoint (video)Qlik Sense - User Data Upload (video)Qlik Sense - The Developer Hub (video) ...View More

I am sure that I am not the only one that at some point a Qlik Sense table was needed to be exported into a spreadsheet. While working with the APIs like Mashup and Engine API, this may get a little trivial, especially when we have so many solutions on the the web but not one that works in all major browsers and especially on our Qlik Sense table Object.Even though this sound very simple and a simple copy and paste would do, here is a proper way of getting only the relevant fields displayed onto our webpage. This works on a simple html table as well as with a Qlik Sense Table Object.In my previous posts I have showed you on how to create a webpage with Mashup API Creating a webpage based on the Qlik Sense Desktop Mashup API and for styling purposes, how to beautify your page with bootstrap Aligning objects and making a mashup responsive using Twitter’s Bootstrap and jqueryFor this project I used an existing app for College Football Rankings Preseason College Football Rankings vs. Final Rankings Over The Years May Surprise You - RantSportsNow, lets start by connecting to our app and getting the table object. var me = { config: { host: window.location.host, prefix: "/", port: 443, isSecure: true, }, vars: { id: '1b4194fd-0ace-4934-80ff-2c679b19624e' }, data: {}, obj: { qlik: null, app: null }, init: function () { require.config( { baseUrl: ( me.config.isSecure ? "https://" : "http://" ) + me.config.host + (me.config.port ? ":" + me.config.port: "") + me.config.prefix + "resources" }); }, boot: function () { me.init(); me.log('Boot', 'Success!'); require(['js/qlik'], function (qlik) { me.obj.qlik = qlik; qlik.setOnError( function ( error ) { alert( error.message ); } ); // Get the Qlik Sense Object Table me.obj.app = qlik.openApp(me.vars.id, me.config); } ); }, Now lets put the html code that will host the object. <div class="row"> <div class="col-md-12"> <article style="height: 250px" class="qvobject" data-qvid="DBujmm" id="DBujmm"></article> </div> </div> Now place the object with our code // Get the Qlik Sense Table Object me.obj.app.getObject(document.getElementById('DBujmm'), 'DBujmm'); Lets try and get the raw data now from a HyperQube so we can create our custom html table. I have also created here a function that refactors data since I like to be working with more meaningful objects. // Get raw data with HyperQube to create the Table getData: function (callback) { me.obj.app.createCube({ qDimensions : [{ qDef : { qFieldDefs : ["School"] } },{ qDef : { qFieldDefs : ["Conference"] } } ], qMeasures : [ { "qLabel": "# Preseason Top 10", "qLibraryId": "HdsZnjL", "qSortBy": { "qSortByState": 0, "qSortByFrequency": 0, "qSortByNumeric": 0, "qSortByAscii": 1, "qSortByLoadOrder": 0, "qSortByExpression": 0, "qExpression": { "qv": " " } } }, { "qLabel": "# Postseason Top 10", "qLibraryId": "tEknwb", "qSortBy": { "qSortByState": 0, "qSortByFrequency": 0, "qSortByNumeric": 0, "qSortByAscii": 1, "qSortByLoadOrder": 0, "qSortByExpression": 0, "qExpression": { "qv": " " } } } ], qInitialDataFetch : [{ qTop : 0, qLeft : 0, qHeight : 20, qWidth : 5 }] }, function(reply) { me.log('getData', 'Success!'); me.data.hq = reply.qHyperCube.qDataPages[0].qMatrix; me.refactorData(); callback(true); }); }, // Refactor Data to a more readable format rather than qText etc. refactorData: function () { var data = []; $.each(me.data.hq, function(key, value) { data[key] = {}; data[key].school = value[0].qText; data[key].conference = value[1].qText; data[key].pre10 = value[2].qText; data[key].post10 = value[3].qText; }); me.data.rf = data; }, Now lets create the html holder for this table including the column headers. <div class="row"> <div class="col-md-12"> <table id="tableData"> <tr> <th>Team Name</th> <th>Times in Preseason Top 10</th> <th>Times in Postseason Top 10</th> <th>Times in Pre & Postseason Top 10s</th> <th></th> <th>Conference</th> </tr> </table> </div> </div> Here is the code that will generate the table // Prepare Data for Display displayData: function () { $.each(me.data.rf, function(key, value) { var html = '<tr>\ <td>' + value.school + '</td>\ <td>' + value.pre10 + '</td>\ <td>' + value.post10 + '</td>\ <td></td>\ <td></td>\ <td>' + value.conference + '</td>\ </tr>'; $('#tableData').append(html); }); // After everything is rendered, enable the buttons for export $('#export').removeClass('disabled'); $('#exportSense').removeClass('disabled'); }, OK. So far we have the code that connects to the Qlik Sense App, gets the table as an object and as row data and displays both for us to use. Lets now put the buttons that will export these two tables. The first one is for the Html Table and the second one is for the Qlik Sense Object Table <div class="row"> <div class="col-md-12"> <a href="#" class="btn btn-default disabled" id="export">Export Html Table to CSV</a> </div> </div> <div class="row"> <div class="col-md-12"> <a href="#" class="btn btn-default disabled" id="exportSense">Export Sense Table to CSV</a> </div> </div> As you can see I a gave them 2 different classes so I can control the on click events. I can definitely have more efficient way of handling the script execution but for this example it is better if I keep them separate.Here is the code that handles the events. We are passing to the exportTableToCSV methis $(".export").on('click', function (event) { me.exportTableToCSV.apply(this, [$('#tableData'), 'QlikSenseExport.csv']); }); $(".exportSense").on('click', function (event) { me.exportTableToCSV.apply(this, [$('.qv-object-table'), 'QlikSenseExport.csv']); }); Finally, here is the code that handles the csv generation and downloading. exportTableToCSV: function ($table, filename) { var $rows = $table.find('tr:has(th), tr:has(td)'), // Temporary delimiter characters unlikely to be typed by keyboard // This is to avoid accidentally splitting the actual contents tmpColDelim = String.fromCharCode(11), // vertical tab character tmpRowDelim = String.fromCharCode(0), // null character // actual delimiter characters for CSV format colDelim = '","', rowDelim = '"\r\n"'; // Grab text from table into CSV formatted string var csv = '"' + $rows.map(function (i, row) { var $row = $(row), // Select all of the TH and TD tags // If its a Sense Object, remove the search column $cols = $row.find('th:not(.qv-st-header-cell-search), td'); return $cols.map(function (j, col) { var $col = $(col), text = $col[0].outerText; text.replace(/"/g, '""'); // escape double quotes return text; }).get().join(tmpColDelim); }).get().join(tmpRowDelim) .replace(/\r?\n|\r/g, '') .split(tmpRowDelim).join(rowDelim) .split(tmpColDelim).join(colDelim) + '"', // Data URI csvData = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csv); // Check if browser is IE if ( window.navigator.msSaveOrOpenBlob && window.Blob ) { var blob = new Blob( [ csv ], { type: "text/csv" } ); navigator.msSaveOrOpenBlob( blob, filename ); } else { $(this) .attr({ 'download': filename, 'href': csvData, 'target': '_blank' }); } me.log('exportTableToCSV', 'Success!'); }, That's it. I hope this will help you to export your tables to a format for your favorite spreadsheet.The Files and the entire working project is athttps://github.com/yianni-ververis/Export-Table-to-CsvAlso, you can view it live atExport to CSV‌Yianni ...View More

In data modelling and in Business Intelligence there is something called connection traps. These are inconsistencies in the data model that sometimes cause problems. This blog post is about describing the fan trap and the chasm trap and how these should be handled in a Qlik data model.

...View More

I've been asked quite a few times about QlikView and Qlik Sense compatibilities, questions such as, how to “migrate” an app from QlikView to Qlik Sense? How compatibles are the two products? Let's explore the possibilities.How toThe procedure to move an app from QlikView to Qlik Sense couldn’t been any simpler, you just need to drag a QVW app (QlikView file extension) and drop it into your Qlik Sense Desktop, that’s it. This will convert the QVW file into a QVF (Qlik Sense app file extension) and load the same data for you.Please note that this process is not reversible so please make sure that you have a secure copy of the QVW file that will be converted.Same load scriptOnce your file is transformed you will find that the Load Script has been ported and Qlik Sense will contain the data from the previous QVW so you can start discovering and create charts right away. That’s it, any data normalization/transformation you created in QlikView will work just fine in Qlik Sense.However, if you hit the reload button right after an app conversion you most likely will find an error popup with a message like this: LOAD statement only works with lib:// paths in this script mode To solve this inconvenient and be able to reload normally you just need to recreate the connection to the data sources using Qlik Sense Data connection libraries or switch on the QlikSense Legacy mode.Are the charts compatible?If you have tried to convert one app from QlikView to Qlik Sense or the other way around, then you already know the answer, no they aren’t. However since expressions are fully compatible, any formula or complex expressions can simply be copied and pasted into the new environment.Dimensions and expressionsJacob Vinzent jvi‌ has created an utility to get all QlikView 11 dimensions and expressions and convert them to Qlik Sense as master items. Check it out at Qlik Branch‌ # Fasttrack QlikView to Qlik Sense.What about variables?If you use variables in QlikView to reduce complexity in some expressions or if your goal is to reuse expressions in multiple objects then you probably should create them as a measure in the Qlik Sense Master Library. For the rest of variables to be automatically migrated we will have to wait a little bit yet... stay tuned.UPDATE: since the release of Qlik Sense 2.1.1 variables (both script and design) are created automatically when migrating an app from QlikView.Can I reuse my QVDs?Yes, you can reuse the QVDs created with QlikView and load the data really fast into Qlik Sense and the other way around.Note: Please note this information applies to the current scenario and current versions (QV11SR2 and Qlik Sense 2.0.2) ...View More

Most hierarchies are dimensional hierarchies. This implies several things: First, you have a many-to-one relationship between the levels: a day belongs to one (and only one) month, a month to one (and only one) quarter, etc. Strictly speaking, it is not a hierarchy unless this condition is fulfilled. Secondly, the hierarchy contains no measures. Instead, numbers are stored in a transactional table that is linked to the dimensional hierarchy. But there is another hierarchy-like structure, the Bill of Materials, the “BoM”. This is a list of items, assemblies and sub-assemblies representing the design of a product or device. Many products are planned and documented with BoMs. A multi-level BoM depicts parent-child relationships and shows the hierarchical structure of the assemblies and their related subcomponents. A multi-level BoM is essentially a nested list whose items are listed to illustrate multiple assemblies within a product. But a BoM is very different from a dimensional hierarchy. It does not have to have a strict many-to-one relationship between the levels. For instance, a specific bearing type can be used in several places. For the BoM, this means that the bearing can have several parents in the hierarchy. Further, each line in the BoM has numbers in it, typically Quantity and Cost. These are measures that should be summed. In a sense, a BoM is more similar to a transaction table than a dimensional table. A BoM can easily be loaded and analyzed with Qlik Sense or QlikView, but there are some challenges: First, the list often lacks a parent reference. Instead, the parent-child relationship is implied by the order of the rows and the Level field, and visualized by indentations. Secondly, aggregating the measures is not straightforward. When summing the costs, the multiplicities of all the nodes above it must be taken into account. In the example in the above table, the wheel assembly uses 2 bearings, and the trailer uses 4 wheel assemblies. Then the trailer obviously needs 8 bearings. In other words: The row for the wheel assembly – and all rows belonging to it – must be looped 4 times when summing the cost. Luckily, both these challenges can be handled in the Qlik script. One possible solution is the following: Hierarchy ( [Part No], Parent, Description ) Load *, Subfield( Path, '/', Level ) as Parent; Load *, Left( Peek( Path ), Index( Peek( Path ) & '/', '/', Level ) -1 ) & '/' & [Part No] as Path While IterNo() <= Units; Load *, If( Frac( Quantity)=0, Quantity, 1 ) as Units, If( Frac( Quantity)=0, 1, Quantity ) as Amount From BoM ; The reference to the parent is created in two steps: First a Path is built using the Level and the Path of the above row. Having the path, it is straightforward to extract the parent id using Subfield(). Further, each row is loaded several times using a while loop. Hence, row 16 (the ball bearings) is loaded twice since its Quantity is 2. But it should be loaded 8 times since the Quantity of its parent (row 6, Wheel assembly) is 4. This multiplication is achieved using the Hierarchy prefix. Finally, the above multiplication algorithm only works for integer quantities. For this reason the bottom Load splits the Quantity into two fields: a field Units that is used in the While loop, and an Amount that is used in the aggregation: Sum( Amount * UnitCost ) However, this means that nodes that have non-integer quantities cannot have any children. If they do, the above algorithm cannot be used, and the cost roll-up must be made a different way. Luckily, this is rarely – or never – the case in real life. Good luck with your Bill of Materials! HIC Further reading related to this topic: Unbalanced, n-level hierarchies ...View More

When you're building a Qlik Sense mashup, you have the option of including the selections bar, which is pretty awesome. The selections bar not only keeps track of and displays selections made, it has step back and step forward functionality to undo or redo selection changes, a search tool, and a drop down selections tool.It's possible, however, that for whatever reason you may not want to use the selections bar, but you still want to give your users some control over selections. The simplest way to do this probably is to include a clear selections button, as we do in quite a few of our mashups. You can see how to do that in a previous post on this blog here - How to create a “clear selections” HTML button for your next mashup project.But if you want to show a little more information on current selections, and give users a little more granular control over what selections they are clearing, you can build a custom selections bar. I'm going to walk through building a very simple selections bar, and you can follow along and build on the idea, adding your own styling and customizing it even further.NOTE - I'm going to assume that you have some prerequisite knowledge of building a mashup. If you do not, here are a few links to get you started -Qlik Sense Workbench. The visualization and Mash-Up EditorQlik Sense Mash-Up API TutorialsQlik Sense Help - Building MashupsThe first thing you need to do is actually get the selections currently made. You can do that like this - app.getList("SelectionObject", function(reply){ // STUFF TO DO IN CALLBACK }); That will get you the selection object as the reply. The rest of our code will go inside of the callback function, which will execute every time the selections change. Now in the callback, we need to grab the DOM node we're going to use for the selections bar, and make sure it's clear of any previous content so that every time the callback function is called we are not appending duplicate content. $selections = $("#selections"); //DOM node to append selections to $selections.html(""); //Clear node of any previous selections Now comes the bulk of our code. We have to loop through the array of fields that have selections, and for each field that has selections, grab some variables, including the field name, number of selections made in field, the total values in the field, a string of the names of the values selected, and something called the selection threshold. If the number of selections is greater than the selection threshold, the selection object will not return the name of every value selected, so at that point we no longer display the names of the values selected, and instead display the number of selections. You could also choose to always display just the number of selections as well, instead of the names of the values selected. Here, I've chosen to display the names of values selected, when possible. //Loop through array of fields that have selections $.each(reply.qSelectionObject.qSelections, function(key, value) { var field = value.qField; //The field name var numSelected = value.qSelectedCount; //Number of selections in field var total = value.qTotal; //Total number of values in field var threshold = value.qSelectionThreshold; //Threshold in which to display a number count instead of each value var selectedStr = value.qSelected; //When numSelected is less than or equal to threshold, a string of the names of each value selected //If numSelected is below or equal to threshold, show string of names of each value selected if (numSelected <= threshold) { var html = ""; html += "<span class='selected-field-container' id='" + field + "'>"; html += "<span class='selected-field'>" + field + ": </span>"; html += selectedStr; html += " <span class='clear-field'>X</span>"; html += "</span>"; $selections.append(html); } // If numSelected is greater than threshold, show the numSelected of total values else { var html = ""; html += "<span class='selected-field-container' id='" + field + "'>"; html += "<span class='selected-field'>" + field + ": </span>"; html += numSelected + " of " + total; html += " <span class='clear-field'>X</span>"; html += "</span>"; $selections.append(html); } }); Notice that I've include a span with class 'clear-field'. We're going to attach an event listener to that, so that when that span is clicked, the corresponding field has its selections cleared. //Event listener on .clear-field to clear that field's selections when clicked $(".clear-field").click(function() { var field = $(this).parent().attr("id"); //Field associated with the .clear-field that was clicked app.field(field).clear(); //API method to clear the field }); And that's it. I've attached a folder of the entire project where you can see all of the code, and you can place it into the extensions folder of Qlik Sense Desktop to see this in action and play around with it (you'll also need the Consumer_Sales.qvf if you don't already have it). ...View More

I am sure that I am not the only one who has added a filter pane to their Qlik Sense app and found a combination of strings and numbers when you were expecting all numeric data. This happened to me recently when I was building an app that had many data sources. I was loading a year field from each of the data sources but I soon realized that the data I was loading was not always numeric. So when I added a filter pane to my app and added the dimension year, I ended up with a list like the image below (on the left). Some of the years were left-aligned and some were right-aligned.One of my data sources had years from 2000 to 2012 which were loading as strings (left-aligned) while my other data source had years from 1990 to 2014 that were right-aligned. My obvious problem is I want each year listed once and to be consistent across all my data sources so that when a user wants to select 2010, they do not have to select both the string and numeric representation of the year.Part of the script that loaded these files looked like this:After some testing I realized that the second table shown above, Consumptionhousehold, was where the string year values were coming from. I did not generate the QVD being used for this app but I have found that sometimes numeric values are loaded as strings if there are additional characters in the original field. For instance, in Excel, a year column may be stored as text if there is a leading single quote. If you look at the image below, you can see that the year in column A is stored as text because it starts with a single quote (‘2015). In column B, the year is numeric because it is just numbers. When both these values are loaded into Qlik Sense, they are evaluated differently and can lead to problems.To resolve this issue I modified my script to evaluate the string year as a date by using the Date# function and then I added the format parameter (‘YYYY’) to indicate how the year is formatted (see image below). Going forward, I added this modification to my script every time I loaded year just to be on the safe side. You can read more about the Date function in blog titled The Date Function.Once this modification was made I ended up with a list of years from 1990 to 2014 and they were all right-aligned and listed only once.While this is a small problem, it can cause confusion when you are building an app especially if you do not realize it has happened. I noticed the issue because I added a filter pane to my app but had I not done that and tried to use the year field in a chart or in set analysis, my results would have been incorrect. The lesson I took from this is to always check to make sure that the data I am loading is loading as expected. I can easily test this by adding filter panes to my app or looking at the data model viewer to ensure that everything is loaded as planned. Good luck scripting.Thanks,Jennell ...View More