When Microsoft unveiled its Windows 8 UI, which they called the ‘Metro’ style, there were whispers of a new underlying design concoction which was happening in an effort to set a trend. The whispers quickly turned into a buzz when Google switched their visual design and UI for all its platforms to bold and contemporary. Then came Apple with its iOS 7 designed flat as paper which turned the big buzz into what was being called a design revolution which was the antithesis of faux bevels, shadows and the real world lookalikes.“There is a profound and enduring beauty in simplicity, in clarity, in efficiency, true simplicity is derived from so much more than just the absence of clutter and ornamentation. It’s about bringing order to complexity” these are words from Sir Jony Ive, the chief designer of all the beautiful Apple products we own. It was just déjà vu for some of us who thought it was like the return of the BAUHAUS.So what exactly are we talking about?It is about a Design style, an approach to UI that has become a mainstream and what everyone seems to be following these days. It can be seen in websites, mobile apps, print, branding, logos, icons and even in dashboards. It is called Flat design.So what exactly is a Flat design and what is not a Flat design?Flat design is essential visual elements that are literally flat, they are without any shadows, bevels and gradients. Apart from its elements Flat is minimalistic in its style and places greater emphasis on colors, typography and imagery as core design principles. Basically it is striping off the ornamentation which doesn’t support the UI and doesn’t mimic real-life objects. Something like this...And this..What is not flat design is obviously completely the opposite, and the word for it is “Skeuomorphism”. Skeuomorphism is something that mimics a real-life object, just like the previous Apple UIs before iOS7. Something like this…As opposed to this.It is obviously a design language of the present times but there are certain pitfalls that come along with it that are hard to ignore. They tend to blur the line between navigational elements and aesthetic elements within a design. This means certain UI elements like buttons and toggles merge into the background and cannot be recognized quickly as action items. But again, it depends on how they are designed.Design is a combination of science and aesthetics; however, visual design is also a lot of personal aesthetic choices we make to support the user interface in becoming seamless. My style choice may not be someone else’s. As a designer, I like following the trends to avoid looking like a pair of bell bottom pants in the age of skinny jeans.Another argument for moving away from the age old Skeuomorphism is that we have come such a long way in the digital world that the need to relate to real-life objects in the digital sense seems redundant.So, what should one follow? You can follow your own style of design as long as it justifies the purpose. And if you are the fashionable type then just remember to do a contextual based design, which means do it where necessary and strip off where unnecessary. Just like how Google gets it right, a little shadow where needed but mostly flat. ...View More

A common situation when loading data into a Qlik document is that the data model contains several dates. For instance, in order data you often have one order date, one required date and one shipped date. This means that one single order can have multiple dates; in my example one OrderDate, one RequiredDate and several ShippedDates - if the order is split into several shipments: So, how would you link a master calendar to this? Well, the question is incorrectly posed. You should not use one single master calendar for this. You should use several. You should create three master calendars. The reason is that the different dates are indeed different attributes, and you don’t want to treat them as the same date. By creating several master calendars, you will enable your users to make advanced selections like “orders placed in April but delivered in June”. See more on Why You sometimes should Load a Master Table several times. Your data model will then look like this: But several different master calendars will not solve all problems. You can for instance not plot ordered amount and shipped amount in the same graph using a common time axis. For this you need a date that can represent all three dates – you need a Canonical Date. This is how you create it: First you must find a table with a grain fine enough; a table where each record only has one value of each date type associated. In my example this would be the OrderLines table, since a specific order line uniquely defines all three dates. Compare this with the Orders table, where a specific order uniquely defines OrderDate and RequiredDate, but still can have several values in ShippedDate. The Orders table does not have a grain fine enough. This table should link to a new table – a Date bridge – that lists all possible dates for each key value, i.e. a specific OrderLineID has three different canonical dates associated with it. Finally, you create a master calendar for the canonical date field. You may need to use ApplyMap() to create this table, e.g. using the following script: DateBridge: LoadOrderLineID,Applymap('OrderID2OrderDate',OrderID,Null()) as CanonicalDate,'Order' as DateType Resident OrderLines; LoadOrderLineID,Applymap('OrderID2RequiredDate',OrderID,Null()) as CanonicalDate,'Required' as DateType Resident OrderLines; LoadOrderLineID,ShippedDate as CanonicalDate,'Shipped' as DateType Resident OrderLines; If you now want to make a chart comparing ordered and shipped amounts, all you need to do is to create it using a canonical calendar field as dimension, and two expressions that contain Set Analysis expressions: Sum( {$<DateType={'Order'}>} Amount ) Sum( {$<DateType={'Shipped'}>} Amount ) The canonical calendar fields are excellent to use as dimensions in charts, but are somewhat confusing when used for selections. For this, the fields from the standard calendars are often better. Summary: Create a master calendar for each date. Use these for list boxes and selections. Create a canonical date with a canonical calendar. Use these fields as dimension in charts. Use the DateType field in a Set Expression in the charts. A good alternative description of the same problem can be found here. Thank you, Rob, for inspiration and good discussions. HIC ...View More

Before the ODBC interface to databases was developed in the late 80:s and early 90:s, it was difficult to connect to a database and import data. But thanks to Microsoft and some other DB vendors, we got an open interface with which we still today can load data from almost any database.But, some aspects of the Windows ODBC implementation are confusing…When ODBC was developed, computers were running DOS or Windows 3.1, i.e. 16-bit programs, and as a consequence, ODBC was also 16-bit. Then came 32-bit programs and it got messy: You could not use the 16-bit ODBC with your 32-bit programs – you had to use the 32-bit ODBC. But at least there were two icons for the two different ODBC:s in the control panel, so it was clear what you needed to do to configure the right driver.Today, with 64-bit operating systems, we have a similar situation: There are both 32-bit and 64-bit programs. But it has become even more confusing, because there is only one ODBC icon in the control panel. And many users do not know that this is just for the 64-bit ODBC.Facts:A program always needs the correct ODBC driver: A 64-bit driver for 64-bit programs, and a 32-bit driver for 32-bit programs.The 64-bit drivers are configured using C:\Windows\System32\odbcad32.exe (Can be started from the ControlPanel ˃ Adminstrative Tools > Data Sources (ODBC).)The 32-bit drivers are configured using C:\Windows\SysWoW64\odbcad32.exe (Cannot be started from the control panel.)And, no, there are no typos in the paths and the file names. They are really named like that. Trust me. The 32-bit administrator really is found in SysWoW64, and the 64-bit is found in System32. Microsoft cannot have had any usability tests here...To simplify things, QlikView has menu items for both ODBC administrators in the script editor:With these, you can open the correct ODBC Administrator easily. But note – the two administrators cannot run at the same time. You need to close the open one before you can open the other.Further, when you create your data source, you can choose between creating a User DSN or a System DSN. The latter can be accessed by any user, while the former can be accessed only by you.But unfortunately, that is not the only difference between the two. There is a second, confusing difference: The list of User Data Sources is a mixture between 32-bit and 64-bit User DSN:s, so when you create the connect string, you will also see unusable data sources (see below). If you choose an unusable data source, you will get an error message that talks about an “architecture mismatch”. To avoid mixed lists, you should never create any User DSN:s. Always create System DSN:s.Finally, a couple of words on how it is that QlikView can use both 32- and 64-bit ODBC drivers: The QV.exe itself never connects to ODBC. Instead it launches a separate process. Depending on whether CONNECT32 or CONNECT64 is used, QVConnect32.exe or QVConnect64.exe is launched, which connects to ODBC and streams the data in QVX format to the QV.exe.With this solution it is possible to use 32-bit ODBC drivers together with your 64-bit QlikView.HIC ...View More

Continuing the line charts topic I've started a few weeks ago, let see how to adjust a line chart to help us to see what we want to.Displaying what is importantIn the example below I want to display Sales evolution by store over time, but it happens that this company has too many stores (lines) to easily identify them individually in the chart.I still can get some value out of this chart; global trend is still visible. Seems like sales are decreasing over time, but details are lost in the color mess.As color is not helping me to better decode this chart, I will unify line colors.Now I can see much better my company’s trend, sales amount is decreasing, based on the overlap of individual store behaviors but details are still hidden in green.Selecting a store I could isolate it and get its details. This is great and fast, but it avoids me from seeing the selected store in context. What I would like to see is the sales evolution of particular store vs all others.The next step will be to avoid the chart to be filtered out when a store name is selected, to do so I will include set analysis in my expression.Original expression: sum(Sales) New expression: sum({<Store=, [Store Name]=>} Sales) By doing that my chart won’t be filtered by Store or Store Name selectionsNow, what I need is to highlight the store within the chart. Again I will use the Background Color properties for the expression to achieve that.if(isnull(only([Store Name])) ,ARGB(20,200,200,200),ARGB(190,43, 123, 70)) This expression will gray out all non-selected stores and highlight our selection(s).With this new chart, my selections will help me to see store details and to keep selected store(s) in context. I can see the global trend and compare it with any store just by searching and selecting.Extra tip: If you look carefully at the two charts above, you may notice a slightly color difference in the second image dark green line, this is because the green line is under several semi-transparent gray lines making it look little bit different.Sorting the dimension will help you to prevent this. In this case I’m sorting Store Name by sales amount using the following expression.aggr(sum(Sales),[Store Name]) You can see this chart working as part of Retailer EPOS Data Analysis demo.Line charts beyond your imaginationThese are some creative and really cool non-standard uses of line charts I have seen lately:Parallel Co-Ordinates ChartThis chart variation was firstly introduced by Alistair Eaves some time ago. The chart is great for showing data paths between various dimensions; letting people to understand something so hard to see as flows dynamics in the data. Check out the demoDynamic Network Flow ChartsIf you are a frequent flyer you will probably have read all magazines in your front pocket. One of my favorites readings on board are these maps with all the routes airlines operate through the world. Matthew Crowther stretched out QlikView line chart object to display information in the same way but keeping the QV soul (association) in it. If you want to know how to do it, you should read: New Qlikview Chart Type: Dynamic Network Flow Charts | QVDesignThe Associated Curve ChartBased on the same principles but pushing things a little bit more Matthew presented this new chart, he called Associated Curve Chart. You can check it out at Matthew's blog Enjoy Qliking!AMZ ...View More

In many of the demos that I have done, I have added a customizable straight table or ad-hoc report where the user can select the dimensions and measures that should appear in the chart. This is often helpful and can replace multiple detailed straight tables in an application. Creating the customizable chart involves simply loading the dimensions and measures that the users can pick from and then adding a straight table with conditional dimensions and expressions.In the Call Detail Record Analysis demo a customized report was added to the application. This is how the ad-hoc report looks with some dimensions and metrics selected.The steps taken to build this report were:Load dimensions and metrics that should appear in the list box for the user to choose from. In this example, this was done by adding two inline tables with the script below.Then list boxes for _dimension and _metrics were added to the report sheet with the LED Check boxes selection style. Next a straight table is added to the sheet with the following conditional calculation: The chart will be displayed if at least 1 dimension and 1 metric are selected from the list boxes.Next the dimensions were added to the table. Each dimension in the list box was added to the chart with a conditional expression so that the dimension is only displayed if it is selected from the Dimensions list box. The same was done for each measure listed. In five easy steps, a customizable straight table or report can be added to your QlikView application providing users the ability to display a report with the data they want to see at the time. This gives the user more flexibility and reduced the need to add detailed straight table charts to the sheets in the application.You can read more about this topic in my Technical Brief which has more detailed step-by-step instructions. Have fun!Thanks,JMC ...View More

Week numbers are often used in calendars, although not as commonly in some countries as in others. In northern Europe, it is very common to refer to a week by its number, but in many other countries it is not used at all. Just as with the week start, week numbers are defined differently depending on country, so you may need to add code in QlikView to generate your own week numbers. So, how do you count the weeks? Is Jan 1st always part of week one? Not necessarily. If week 53 starts as late as Dec 28th, does Jan 1st also belong to week 53? Sometimes, yes. There is a definition made by the International Organization for Standardization (ISO 8601) that QlikView uses to calculate week numbers. It states that The week starts on a Monday. A week is always unbroken.I.e. some years week 1 starts already in December, and in other years week 52 or 53 continues into January. Week 1 always contains Jan 4th. Or, differently put: Week 1 always has at least 4 days in January. A third way to say the same thing is: The first Thursday of the year always lies in week 1. These three bullets define the three parameters you need to define general week numbers: Set vCal_FD = 0; // First Day of the week {0=Mon, 1=Tue, ... , 6=Sun} Set vCal_BW = 0; // Broken Weeks allowed {0=No, 1=Yes} Set vCal_RD = 4; // Reference day = This day in Jan defines week one {1..7} How the week start - the first parameter - influences the week number can be seen in the following table. It shows how the week number would change for the days around New Year 2013 if different week starts are used. The other parameters are kept constant. The second parameter concerns whether or not broken weeks should be used. If they are, a new week number will always be used on Jan 1st, and as a consequence the first and last weeks of the year can have less than 7 days. And finally, the third parameter, the reference day. It defines which day that always belongs to week 1. In the table below, the reference day is 4; hence Jan 4th always belongs to week 1, which can be clearly seen. This number also defines the minimal number of days of week 1 that fall in the new year. The ISO standard is thus a 0/0/4 week numbering. In countries where Sunday is used as first day of the week, I have seen several different variants: 6/1/1, 6/0/3 and 6/0/4. If you copy the above parameters to your QlikView script and the following lines to your Master Calendar definition, you can redefine the week numbers any way you want: Load *, Div( Date - WeekStart( WeekYearRefDate, 0, $(vCal_FD) ) + 7, 7 ) as WeekNumber, Year( WeekYearRefDate ) as WeekYear; Load *, Date( YearStart( If( $(vCal_BW), Date, WeekRefDate )) + $(vCal_RD) - 1) as WeekYearRefDate ; Load *, Date( WeekStart( Date, 1, $(vCal_FD) ) - $(vCal_RD) ) as WeekRefDate ; The fields WeekYearRefDate (Jan 4th in the ISO definition) and WeekRefDate (the Thursday of the week in the ISO definition) are really not necessary, but the expressions become somewhat simpler if these are used. Until we get a general week numbering functionality built into the QlikView standard functions (and, yes, we are looking into this) you will have to redefine the week numbers using the above script. Good luck! HIC Further reading related to this topic: Ancient Gods and Modern Days Redefining the Week Start Redefining the Week Start in Qlik Sense Qlik Sense – Date &amp; Time ...View More

Six days thou shalt work, but on the seventh day thou shalt rest. [Exodus 34:21]The idea that you should rest on the seventh day is a central concept in both Christianity and Judaism. But which weekday is the seventh day of the week? And which day is the first?The old texts of the Abrahamic religions clearly consider the Sabbath – Saturday – as the seventh day of the week. This day is also still today the resting day for Jews around the world. The same texts also describe how Adam was created on the sixth day, which is one of the reasons why Friday is the day of congregation and prayers for Muslims.Hence, these two religions agree on the numbering of week days: Friday is the sixth day of the week and Saturday is the seventh.However, in the rest of the world, the situation is more confusing: Although Sunday is observed as resting day in most countries, there is a disagreement on whether Sunday is the first or the seventh day of the week. In North America, Sunday is the first day of the week, but in many European countries it is the last day of the week. According to the International Organization for Standardization (ISO 8601), the week starts on a Monday, and Sunday is thus the seventh and last day of the week.How weekdays are ordered makes a difference in all Business Intelligence applications, most notably in how list boxes and charts are sorted. Note the order of the week days in the list boxes below. In the left one, Sunday is on top of the list and in the right one it is Monday. This difference can also be seen in calendar displays, used in many types of software and on many sites in the web. Again, note the order of the week days. QlikView uses ISO 8601 to define the week days and the week start. WeekDay() returns the name of the week day (as defined in the variable DayNames) and a number from 0 to 6; Monday being day number 0. WeekStart() returns the date of the Monday immediately before the date supplied as parameter.If you want redefine this, e.g. if you want QlikView to show Sunday as the first day of the week – here’s how you do it:Start by creating a variable that defines the beginning of the week: Set vCal_FD = 6; // First Day of the week (0=Mon, 1=Tue, ... , 6=Sun)The WeekStart() function has an offset parameter, and if you use your variable here, you can redefine how the function works and get the week start on the correct day: WeekStart( Date, 0, $(vCal_FD) ) as WeekStartThe WeekDay() function, however, cannot take any offset parameter, so you need to define the week day using the Dual() function: Dual( WeekDay( Date ), Mod( WeekDay( Date - $(vCal_FD) ), 7 ) +1 ) as WeekDayUsing these two expressions in your Master Calendar instead of the standard function calls, you can redefine the week start to any of the week days.HICFurther reading related to this topic:Ancient Gods and Modern DaysRedefining the Week NumbersRedefining the Week Start in Qlik Sense ...View More

Pre-attentive processing, as the name suggests is the initial stage of processing information by our brain where certain characteristics are immediately detected without focusing on an object. This act is done quickly and effortlessly where the brain tries to recognize certain visual attributes that make things stand out or show groupings of similar objects. Color, shapes, position, orientation, proximity, size and motion are some of the elements that can be easily detected pre-attentively by the human mind. The examples below show how the brain immediately spots anomalies because of their attributes.While designing a dashboard we rely a lot on showing visual attributes of data for quick and easy detection so pre-attentive processing becomes an important aspect to consider. The ability of the human mind to recognize and process information at light speed can be used advantageously in order to show data outliers and similarities in data in a dashboard.Applying visual attributes to a dashboard like a bright color or an icon that stands out from the rest of the information can justify the purpose of a dashboard making it easy for a user to gauge the situation at a glance. As shown in the example below, color and icon is used as an attribute to highlight numbers that need attention.Color is a strong perceptive attribute but there are others that vary in intensity, like difference in shape is not as striking as contrasting colors. Depending on what information needs to be shown, whether qualitative or quantitative, various attributes can be applied.Visually encoding data for rapid perception can make information consumption in a dashboard extremely easy and convenient for a user. And since a dashboard is a summary or high-level information providing system, it is important to strive to be as visually informative as possible to target the pre-attentive senses in a user.So, the use of pre-attentive information processing techniques while designing dashboards can not only justify the purpose of the dashboard but also help in projecting information as needed. ...View More

A common situation in Business Intelligence is that you have data in different data sources. It could e.g. be that you have several data systems within your company or that you have some data in an Excel spreadsheet in addition to the data in your database.In any case, you want to load data from several sources and view them in a coherent way. This is sometimes referred to as merging data or blending data.Not all BI tools can do this – you sometimes have to rely on external tools or SQL to do this prior to loading the data into your BI tool. QlikView, however, can do this easily.If you have two different database systems, you need two different connect strings in the script: ODBC CONNECT TO <Database 1>; SQL SELECT * FROM TableA; ODBC CONNECT TO <Database 2>; SQL SELECT * FROM TableB;At any place in the script, a SELECT statement will use the latest CONNECT string. In addition, LOAD statements will load data from files, disregarding the CONNECT statement. This way you can merge data from any number of databases and any number of files. Simple!The next question is how to merge the two tables. In principle there are two ways to do this: Concatenating them (a union) or linking them (joining). Which one to use, depends on the situation.Concatenation should be used, if you have two tables with basically the same type of entity, but different data sets; for example “Customers in Europe” and “Customers in North America”. In this case, you want both tables to be merged into one. See picture above. The script then becomes: ODBC CONNECT TO <Database 1>; SQL SELECT * FROM Customers; ODBC CONNECT TO <Database 2>; Concatenate SQL SELECT * FROM Customers;But if you instead have a situation where the tables contain different entities, and a selection of an entity in one of the tables should imply one or several entities in the other table, then you should usually link the tables.The script then becomes: ODBC CONNECT TO <Database 1>; SQL SELECT * FROM Customers; ODBC CONNECT TO <Database 2>; SQL SELECT * FROM Orders;Linking tables is from a logical-mathematical perspective identical to an outer join, but QlikView keeps the two tables separate. Keeping them separate has the advantage that calculations are made in the “right table”.Not all BI tools can perform an outer join as easily as this. I recently read an article about how to join an “Opportunities” table with a “Leads” table using a competing tool, and it was all but simple. But with QlikView, it is straightforward: Just make sure that the linking key field is named the same in both tables (and that no other fields are) and it will work right away.The ability of QlikView to load data from any number of sources and merge it any way you want is one of the major strengths of QlikView and its script. Use it.HICSee also Wizards vs. Scripts ...View More

QlikView Standard Visualization objects have much more to offer than standard out of the box settings. This is why data centric apps developers love QlikView so much, because you can dig into every object properties expanding their usage case scenarios.This is the case of the line chart; probably one of the oldest visualization methods available. It´s a pretty simple and straightforward way to represent data - often used to visualize a trend in data over intervals of time.In-Chart LegendThis is a simple space saver trick; it will help you to replace QlikView standard legend with a customized in-chart legend.Look at the chart below. It represent sales in two countries US and UK. As you can see, we don't have too much space to plot the chart and the legend. QlikView will automatically adjust the chart area to the real state in the object. In this case that will make my chart area to be really small.One approach could be to move the legend to the bottom. This will make my chart area much bigger, specially true for long label scenarios. On the other side, by reducing the Y axis the lines look much flatter, and in some situations that could mean that some users wont perceive small variations in the chart. Remember: To move or/and size chart components as legends, text in charts, and chart area, press the Shift and Ctrl keys and keep them depressed while a chart is active. You will enter the chart layout edit mode. In edit mode, thin red rectangles will appear around those components of the chart that can be sized or moved. Use the mouse drag and drop technique to move things around. What can we do to get the best of both worlds?Another alternative could be something like the chart below, where legends appear right next to the each line.To tune a line chart to look like the image above, you will need to follow the next steps:Create a standard line chart as in the picture.Use one expression per line, we cannot use this method when the lines are created based on dimensions.Use Dual() Duplicate each expression and set the definition toif(Num= max(totalNum),dual('US',[United States of America]))Where, Num is the dimension name,'US' will be the text displayed in the chart and [United States of America] is the number I calculated with my first expression.To finish, remember to check Symbol and Values on Data Points for the expression.Pros:This method will allow you to create mini sparkline using standard line charts.The most obvious advantage will be to get more space for your chart area.Due to in context information, in-Chart legend style will allow users to quickly consume the chart. Users doesn't need to apart their eyes from the chart to read the legend.Cons:Lack of dynamism: you have to create one expression per line displayed, rather than letting QlikView to include new elements as they came to the data model.In situations where multiple lines have to be displayed this method may create confusion by overlapping the legends labels.Hard-coding expression is required.I will post a second part on this topic with another trick soon, stay tuned!Enjoy Qliking!AMZPS: if you have tricks you want to share with our Community left us a comment ...View More