How To: QlikView Running Counts Using FirstSortedValue

I can tell you from first-hand experience, getting running counts or balances to calculate accurately in QlikView is not easy. There are data model concerns and then complicated aggregations to deal with. In this post, I will give you a front-end solution “template” for handling running balances in QlikView discussing the nuances with a few use-cases. I will focus on the use of the FirstSortedValue and Aggr functions.

This will work in both QlikView and Qlik Sense. You can download the application I used along with the spreadsheet source here.

FirstSortedValue-Testing-2.zip

Running Balances: Use Cases Full of Nuance

From a front-end perspective, running counts have not generally been a “one size fits all” solution. For example, if you are reporting a single number: Current Balance, that is easy enough using set analysis. But a different expression would be used for Current Balances by Account. And yet another calculation would be used for Balances by Account Over Time. If you want the user to be able to look at the balances daily rather than monthly, change to select a historical date, or cycle date dimensions (alternative dimensions in Qlik Sense) you are really in trouble.

I have created somewhat of a template expression that can be adapted to most use-cases.

Before We Get to the Fun Stuff: Data Model Concerns

Your data model must first be built to support the questions the user will need to answer. Configuring the data model to support a small question like “How many tickets do I have open today?” is very simple. Building the data model to support a question like “How many tickets were open each day by status in the month of June?” is more complex.

Your data model should house one row for each countable dimension id for each time value that is pertinent for that id.

You should build a row in your data model for each dimension id (ie: Ticket ID, Account Number) for each value of time increment required. For example, if we are reporting on open helpdesk tickets by day, we will need a table that contains a row for every ticket id for every day the ticket was open. If we are only reporting this by month, we will only need a row for each ticket for each month.

The specifics of this consideration will greatly affect the size of our data model. Consider an app with 5 years of history, an average of 500 tickets open at a time with each ticket being in an opened status for 5 days. We will require a table with 4.5 million records (5 years * 365 days * 500 ticket average * 5 days open average).

If the data is not already presented like that, there are techniques to make this happen. We will save them for another post. I will assume for now that you can get your data set up as described above.

FirstSortedValue Solution Template

I think the best approach is to look at the solution template and its components. Then we can apply it to some use cases. This will create accurate running totals in any chart with any combination of dimensions which allows us to run totals for a point in time or over time.

Template:

if(max(Date_Base)=max(total <$(='[‘ & getobjectfield(0) & ‘]’)> Date_Base),
firstsortedvalue(aggr(sum(total < Date_Base,Secondary_Dim> m_ticket), Date_Base, Secondary_Dim), –aggr(max(Date_Base), Secondary_Dim, Date_Base)))

Things you will need to adjust to your needs:

sum(m_ticket)Use your base expression. This would normally be a count or a sum. You will need to insert the total and associated group by field(s)  into your expression.
<$(= [ & getobjectfield(0) & ])>Your decision here is to use as-is or omit. This section is needed when the chart is run over time. This assumes that the date dimension is the primary (first) dimension in the chart.
Date_BaseAnywhere we see Date_Base (6 spots), you will need to substitute your “finest” time dimension from your data model or specifically, the time increment where the expression would evaluate correctly. This has nothing to do with any dimensions you decide to utilize in your chart.
Secondary_Dim[,nth_Dim]Where you see Owner, you will substitute any secondary dimensions from your chart that are not related to the Date dimension separated by commas.

I can attempt to explain how this works.

Without regard to the actual dimensions in the chart, sum up the tickets grouped by the finest date and secondary chart dimensions aggregated by date and secondary dimensions. Then sort this list in descending order by finest date field aggregated by date and secondary dimensions as well. Now consider each chart dimension combination, finding the first value from the sorted list that can be applied to each dimension row in the chart.

Simple, right?

Front-End Scenarios Using FirstSortedValue

So now we can apply this expression to some examples ranging from simple to complex.

Reporting Current Open Tickets with No Dimension

In this first example, we are actually making it more complicated than it has to be simply because we do not have to consider any dimensions. You could use the expression above or a small set to find the maximum date.

Here is the set example of this without using the universal expression.

Here is the example using the universal FirstSortedValue expression:

Note that I omitted the  <$(= [ & getobjectfield(0) & ])> and there are no secondary dimensions in the total fields or aggr lists.

Reporting Current Open Tickets with Dimensions (not time) 

Here is a slightly more complicated example where I have added a dimension (or more than one dimension). But the key here is that this would be the latest count. We are not wanting (yet) to look at the open tickets over time.

The only difference here is that I have added another field to the total and aggr parameters. You will add whatever fields you have used as dimensions in the chart. Here is the chart with the Ticket dimension.

Reporting Running Count of Open Tickets Over Time

Another class of use-cases would be to establish a count of the open tickets over time. It is this kind of scenario that can get challenging without having this template. In the first example, we will look at the count of Tickets by Status over months.

In this case, I went ahead and subbed the actual time dimension field name so you can see how this works. The Month field corresponds to the first dimension of the chart which I assume would be the time dimension. In addition, I have added the secondary dimension of Status since we want to sum up the tickets by month AND by status.

Reporting Running Count of Open Tickets Over Time with Dynamic Date Field

Of course, I couldn’t stop there. Because my users will want to either drill down or cycle their date field so that they can view the chart by Date or by Week or by Month or by Quarter or by Year. This presents a special challenge because we must actually refer to the dimensions used in the chart within the expression.

So I respond with a special function that will retrieve this field name for me as long as the date cycle is the first field in the chart. GetObjectField will retrieve the chart dimension field name. A parameter of 0 will retrieve the first field. 1 will retrieve the second field, etc.

Here is the final expression for the dynamic date interval chart.

And the chart looks like this. Now we have the ability to add a cycle button to handle the date. And most importantly, it will still be accurate as we change the time dimension.

Final Thoughts

This was quite a struggle to get right. And I am hoping I didn’t make a mistake. But I am equally hopeful that if there is a mistake here or the expression can be further streamlined or optimized that the expert LivingQlik readers out there will help me out.

I HAVE A BIG FAVOR TO ASK YOU!

My goal is to be make LivingQlik a super-helpful resource for as many Qlikies as possible. That involves writing the best possible articles and getting as many eyeballs as we possibly can get on to the site. To that end, if you find LivingQlik to be helpful or informative or entertaining or at least mildly interesting, I am asking you to immediately tell some of your friends and colleagues. Just walk down to the next cubicle and tell your fellow developers to check out our humble site. Or share it on the social platform of your choice or send a link to a friend. Please do it now!

Thanks for reading LivingQlik.

Happy Qliking!

2 comments

QlikView vs Qlik Sense: Are We at the Tipping Point?

Qlik Sense vs QlikView Featured Image

Much was made about Qlik Sense when it first arrived on the scene. I found MANY posts about Qlik Sense’s arrival including a post I wrote for LivingQlik over two years ago. Now that the dust has settled, Qlik Sense has been deployed with many of our customers and the product has been through several updates, I have gained a new perspective on the tool.

It is time for another post on the topic because I have come to realize…We have reached the “Tipping Point”. Qlik Sense is now the tool of choice. How do I know this?  Well I don’t have scientific evidence, but I have tons of anecdotal evidence. I will attempt to lay out the case that not only have we turned the tide with features and usability, but also in the realm of popular opinion, barely.

We have reached the “Tipping Point”. Qlik Sense is now the tool of choice.

Qlik vs. Qlik Sense: The Technical Battle

Round 1 – Security Goes to Qlik Sense

From a server perspective, there are a few great things about Qlik Sense and a few items still lacking for me. From a security standpoint, Qlik Sense is awesome. Today, we can use any user-directory attribute to create rules for access and authorization to any specific aspect of the tool. From data connections and object creation to granular server administration roles, Qlik Sense has it covered. QlikView, on the other-hand, allows for a super-admin and a document admin, and application level access and authentication piggy-backing on NTFS. More granular abilities are either on or off at the server or application level and are not user based.

Round 2 – QlikView Data Reduction – Not Yet in Qlik Sense

QlikView’s Data Reducing and Loop and Reduce data reduction is a great feature. It allows us to create one master document and then split it up and distribute the smaller applications to specific users. Qlik Sense does NOT have that function built in. With that said, Qlik Sense does have an open API that allows developers to create this functionality*. QlikView wins this one with an asterisk. Let’s talk about it.

Round 3 – * Published API’s. Qlik Sense’s Ace in the Hole

Throughout this post, I might refer to the Qlik Sense API’s. In fact, it has become quite the cliché when a developer asks when a feature will be put into Qlik Sense, a Qlik employee will quip, “You can simply build this function out with the API”. And because I don’t have many web developer skills today, that statement rubs me the wrong way. But as painful as it is to admit I don’t have the Web Developer skill set, published API’s are a game-changer making Qlik Sense a platform, rather than just another piece of software.

  • Engine API
  • Extension API
  • Backend API
  • Capability API
  • Repository Service API

You can witness the inspired ideas sprouting in front of us on Qlik Branch which has grown tremendously over the last year. And I believe this is just the beginning. We are starting to see partner generated premium content that will expand the functionality of Qlik Sense to areas that first fill the gaps and then go beyond. I believe we are entering an exciting new stage of development on this front.

But as painful as it is to admit I don’t have the Web Developer skill set, published API’s are a game-changer making Qlik Sense a platform, rather than just another piece of software.

Round 4 – Data Connections

Connections to data sources can be centrally governed in Qlik Sense, unlike QlikView. The part I struggle with here is that we must put Qlik Sense in “Legacy Mode” to utilize Data Connections with variables. But putting Qlik Sense in this mode, in effect, disables the ability to govern the data connections globally. This round is a draw.

QlikView vs. Qlik Sense Front End Design and User Enablement

Round 5 – QlikView’s Customizable Front End Function

In a previous post, we covered all the things we wish that Qlik Sense had. Input Boxes, Dynamic Labels and Curved Trendlines are just a few of things that developers are clamoring for that have existed in QlikView for years.

QlikView simple forecast

QlikView forecast (3 points ahead)

In fact, the QlikView design experience is highly customizable and has grown over years to include many bells and whistles we need and many options we don’t need. Do we really need “Plot Area Border” adjustments? I guess somebody did once. I realize now that the love I have for QlikView’s front-end is kind of an ego-stroke. At first blush, the answer to some “can you make it do this” questions might be no, but a talented developer can probably “tease out” the functionality. It is a badge of pride. I won’t bother making a list of all the things QlikView does that Qlik Sense does not. But it is long. QlikView is a clear winner in regards to front-end design functionality. Again, with subsequent releases and the expanding extension market, this will change, but right now, the winner of this round is definitely QlikView.

Round 6 – Qlik Sense and Responsive Design

Now, this is a big deal for me as I am a design nerd. For years QlikView developers have been forced to format their applications to the “least common denominator” or create entirely separate documents to cater to different screen sizes and aspect ratios. This is a tremendous hassle and sucks up document licenses.

Responsive design in Qlik Sense has been executed with real thought.

We all know by now that Qlik Sense is responsive to device screen size and aspect ratio. And not only is it responsive, it is smart about it. What I mean by that is that the detail of charts and the navigation change as the screen grows smaller. Words turn into icons. Icons disappear. This is not just a rearranging of screen objects. Responsive design in Qlik Sense has been executed with real thought.

Larger aspect vs. smaller aspect in Qlik Sense

This round goes to Qlik Sense.

Round 7 – Qlik Sense User Enablement

QlikView enables users with an object repository. You can build objects and share them out. You can copy and paste document objects and then change them to your liking. You can export data. You can share your screen with another person. There is user-enablement in QlikView. It is in there… But also, it sucks. It is “cludgy” for sure. And the biggest issue is that users must know Qlik expression syntax to confidently create objects. Qlik Sense takes the concept of user enablement up several levels. In fact, the developer utilizes the same interface that the end-users utilize. The user enabled front end is:

  • Uniform for both developers and end-users
  • Drag and Drop
  • Intuitive
  • Governed

The best part of the user-enabled front end is the concept of the Master Library. The developer can create governed dimensions, measures and visualizations and provide this library to users. This is the true enablement of the user as they do not have to know Qlik Sense expression syntax to create great visualizations. Qlik Sense wins this battle for sure.

Qlik Sense vs. QlikView: The Popularity Contest

Round 8 – Qlik Sense is popular with our customers.

As we consult with a new customer, we are leading with Qlik Sense. Some of that is simply pressure from Qlik to sell the shiny new tool. Some of that is that Qlik Sense demos well…really well. The visualizations are great looking and the drag and drop creation of objects is impressive. And then when you show them the responsive design they are sold. The easy web integration is icing on the cake. The other fact that cannot be glossed over is that our existing QlikView customers want to run Qlik Sense in parallel. Part of that impetus is curiosity to test-drive the new interface, but much  of it is driven by the need to get data quickly. Qlik Sense is a good way to satisfy those power users that can’t wait for the QlikView reports to be developed and delivered. Not that QlikView reports take long to develop, but they do take a specific skill set that is not always available in the organization.

Round 9 – I now prefer Qlik Sense when I create an application

Until recently, I was still in the QlikView camp. But with good looking Pivot Charts, sizeable columns in straight tables and the amazing extensions flooding Qlik Branch, I am coming over to the Qlik Sense camp. Don’t get me wrong. I still find the web-based script writing to be slower. And it is difficult to write set analysis expressions without having some ready-made preview. The data model viewer in Qlik Sense is also a little frustrating. It displays at a reasonable size and then zooms out until nobody could read it. Then you must manually zoom it back until you can see the tables. But in the end, Qlik Sense applications look better. The front-end is easy to build. The server is highly customizable. And I am finding that I can say “yes” to more and more questions. It just requires a web developer if the answer is not in the box. The responsive design in Qlik Sense is a knock-out feature. After developing apps in Qlik Sense for a while, upon moving over to QlikView, I experience a little bit of shock, because I am constantly scrolling, zooming and otherwise adjusting the screen. Lastly, I see the extended possibilities. The open API’s, the ease of mashups, and the exploding market of extensions are very powerful. This is not an easy choice, but when I weigh all the factors, the scale has tipped in favor of Qlik Sense.

Round 10 – LivingQlik Readers Have Spoken

So my opinion is one thing. What about other developers out there? I compiled votes from Twitter, Facebook and LinkedIn to get an idea of what other developers thought. I had many very balanced answers, but some people were adamantly in favor of one or the other platform. In total, 24 people voted for QlikView and 25 voted for Qlik Sense. For all intents and purposes, I call that a draw. Here are a few comments from social media that were especially interesting. Paul Verkooijen Like Bert Otter we also go with Qlik Sense for new customers, but still love QlikView because of the flexibility it brings. However Sense gets more mature with every release. Looking forward to the Training Web Development, Mashups, Extensions en Widgets by

Nick Webster next week, maybe I’ll fall in love with Sense to. Andy Patrick Agree with Bert Otter it would be naive to go in to a fresh deployment using QlikView. Sense is so much easier for end users to be productive with. BI deployments should be about the customer and their productivity, needs and ultimately ROI. It should not be about the developers preference.

Bert Otter I do think coming from a QlikView comfort zone, it will take some time to see the possibilities of Qlik Sense instead of comparing features (or tricks?) that may not work in Sense. With the current versions and releases to come, the possibilities are growing. Of course there is a gap for both users and developers / consultants, but this will grow, the longer you wait.

Final Thoughts? 

Has the Pendulum Swung?

Today, Qlik is not forcing us into one tool or the other. But some are seeing this on the horizon. Hugo Simancas For now both, each one has its flavor !, but my prediction is that in a medium time, Qlikview + QlikSense = a single Qlik, ALL IN ONE!

 

Sue Penick QlikView. Sense is still missing features we are using today with QV. So we can’t change until that is resolved. They just need to have one product that does it all. Why wouldn’t you do that?     he good news is that today, clients have a choice. They can choose QlikView or Qlik Sense or both. It will be interesting to see in a year or two, what choices Qlik has made in the development of the two interfaces, the choices that clients have made and the sophistication of the extended solutions around the Qlik products. Regardless of where you fall on the QlikView vs. Qlik Sense debate there is one thing for certain. It is an exciting time for the Qlik ecosystem. Where do you fall on this topic? Contribute your opinion in the comments area below.

Julian Villafuerte over at QlikFreak has also revisited this topic. I guess I am not the only one who struggles with this question. Please do visit his post because it is way funnier than mine.

Happy Qliking!

5 comments

19 Tips All QlikView Beginners Must Know

You download your bright, new, shiny QlikView Desktop. You are ready to start on your special BI journey to undiscovered data islands. The excitement is palpable.

Now what?

Often as a new developer, you don’t know where to start. There is so much to learn. Trying to put the puzzle together in a way that makes sense and is as efficient as possible is not always easy. There is no substitute for a QlikView training course, whether it is on-line or on-site. But to get you started in the right direction, I put together some tips. These are presented in a natural order as you might encounter them in your development cycle.

1 Diagram Your Data Model

I find it tremendously helpful to draw a diagram of my data model before loading ANY data into QlikView. The diagram does not need to be super precise or take hours. Just concentrate on the tables and the fields that will associate them to together. It may seem silly but this does a few things for you. First, it helps you visualize the relationships between the tables. Second, it alerts you to potential problems with synthetic keys and circular references. Thirdly, it helps to clarify which of the tables are your central transactional tables and which ones are the dimensional supporting tables. Lastly, the process helps to identify opportunities to simplify or optimize the data model. You will find that if you take the 10 minutes to diagram your data ahead of time, it will save hours of coding experimentation and mistakes. Here is a diagram I created for an application. You can judge whether this is a great example you should emulate, or the gibberish of a crazy-man (dare I say the latter?)

2 The Design Toolbar

You should probably add the Design Toolbar to your QlikView desktop. This gives you several buttons that you can use to initiate commonly used features like New Sheet, Format Painter, Design Grid and Table Viewer. In the main menu go to View –> Toolbars –> Design

3 Save Before Reload

I am not telling you to manually hit save before you hit reload. Instead I am telling you to set up your preferences so that a save is automatically initiated every time you press reload. This way, you don’t even need to think about it! Because when your script bombs, you want to get back to where you left off. To set this preference, in the dashboard menus, select Settings –> User Preferences. Select the Save tab and then check the box for Save Before Reload.

4 Set up Document Logging

You will become familiar with the Script Execution Window. In particular, you will get familiar with checking the Close button. And at some point you simply opt to select the little check box to automatically close the execution window. So when you need to examine entries in this log, it is helpful to have a copy of that log saved for you. If you again navigate to Settings –> User Preferences and then go to the Design tab, you will find Always Use Logfiles for New Documents. Checking that box will tell QlikView to create a text log in the directory of your QlikView file (qvw).

5 Iterate

Do one thing at a time.

  1. Code a single improvement
  2. Reload
  3. Validate

Many times, I have gotten into trouble by not following this advice. For example, I might load a table, then left join another table to, with a mapping load to get this translation and then multiply it with an existing field and then rename all my fields and transform some date fields…etc. You get the picture. It sounds like I accomplished a lot. But then upon reloading, I get the first of many errors. At this point, I haven’t the slightest idea which functions are causing the issues and then spend 2 hours debugging my work, backtracking along the way to find and correct the problems. If I had just worked in an iterative method, performing one trick at a time, I would have saved about half the time. You might legitimately say, “But Aaron, my reload takes 20 minutes to execute. If I am reloading more often, won’t that cost me way more time?” That brings me to the next few tips.

This concept will help you develop set analysis too.

6 Limited Load

When you are ready to test your script, sometimes I will enter debug mode and then run a limited load. This initiates your script capping off load statements once you reach that threshold of records. This greatly reduces the time spent waiting for your script to execute. The downside of this option is that you might not end up with any associated records between the tables, depending on how many rows you asked for relative to the size of the data. I usually enter 10,000 or 100,000 to get a representative data set. Even 100,000 will still load very quickly. For that reason, the limited load option is good for testing your script logic, but not great for doing data validation.

7 Use QVD Extracts

This is probably one of the best tips for QlikView newbies. Much of the reload time in QlikView comes from connecting to external data sources, like SQL databases. It is not that SQL is slow. It’s just that establishing a connection to another server in the network and then executing a query that returns millions of records will always take time regardless of the database. Instead, I use QVDs. QVDs are proprietary QlikView data files that reside on a file server. They hold one table’s worth of data. They will load into QlikView way faster than the same amount of data coming from an external database. So what I do is load all the data from the database tables one time and store that table in its raw form into QVDs. Then I simply comment out the SQL queries and replace them with the QVD locations, thereafter doing all my development from that QVD. This will save you all sorts of development time. When you have finished all your development and are ready to promote the app, you can switch the locations back to the original database location. In the first screenshot, you can see how I extracted and stored into QVD with my first reload. Then I adjust the script and develop with my QVDs from then on. My subsequent reloads will be much faster.     Here is an article that explains various QVD strategies including data extraction for development: LivingQlik: Learn How to Create a Layered QlikView Architecture

8 Use Path Variables

The idea of path variables is to make it easier to migrate your applications to different folders, or different servers. Whenever we load files located in file servers like qvd’s or MS Excel files, for example, these paths either need to be completely written out (fully qualified path) or relative to the location of the qvw. This is fine, but it also means that as we migrate a qvw from a development server to a production server, I must either change all the paths or I have to ensure that the relative folder structure is exactly the same between the two environments. We can avoid this issue altogether by simply creating all the paths I need for all environments and then selectively assigning them to a variable.

9 Use Standardized Naming Conventions

It is a good idea to use standard naming conventions for keys, dimension fields, expression fields and variables. This practice will make it easier to create your dashboards. Your dimensions will all be labeled for your users. Keys will be prepended so they appear at the top of lists. And metric fields will easily auto-populate in your expression dialogs. All of this adds up to time savings for you and makes you look like a professional. QlikView autopopulates field names Here is a full method for naming your fields appropriately: LivingQlik: QlikView Field Naming Best Practices

10 Concatenate Your Fact Tables

All

QlikView developers quickly find themselves in a mess of tables with circular references and synthetic keys galore. Learn the difference between your fact tables and your dimensional tables. Consider concatenating your fact tables together with common keys. This will greatly simplify your data model, reducing or eliminating synthetic keys and circular references.   No doubt this is a complex topic. Here are some resources to help you. LivingQlik: Link Table vs. Concatenation – A Case of Mixed Granularity Qlik Community: Fact Table with Mixed Granularity Qlik Central: Should I Link or Concatenate

11 Mapping Load is Your Friend

As I have said over and over, mapping load is my favorite QlikView scripting function. The main reason this function stands out is because it can be used for a variety of use-cases where left join is less optimum or won’t work. The idea is simple. We can substitute in a value from a mapped table to a recipient table. But we also have the ability to specify an alternate substitution when no match is found. You can read all about mapping load here: LivingQlik: The Complete Guide to QlikView Mapping Load Mapping Load - Syntax Example  

12 Limit Your Data

Once you start loading tables, you will be quickly overwhelmed in the front end. “There are so many fields”. I find it helpful solely from a user/development perspective to reduce the number of fields in my QlikView documents. This also saves vital active memory (RAM) on our QlikView server or our local desktop if opening locally. If you do not have a need for the field in the UI or need it for a key, get rid of it! I do the same thing for extra rows. Why bring in 20 years of data when I am only concerned with the last 6 months? Use where clauses to trim down the data tables. This two-sided approach (rows and columns) will pay off dividends in usability, ease of maintenance and performance. The techniques for this are covered completely here: LivingQlik: Reducing Rows & Columns in your Qlik Data Model

13 Learn to Create a Calendar for Your Apps

Most QlikView applications utilize time as a dimension. And usually these users will need to divide facts by various time dimensions including Year, Month, Week, etc. One skill you need to pick up is adapting a calendar to your Qlik apps. This usually involves identifying and formatting your fact table date field and loading in a generic script to create your Calendar table. Once you do this a few times, it becomes pretty simple. LivingQlik: How To Add Useful Date Groups to your Qlik Calendar

14 Tableboxes Are a Development Tool

I rarely use the Tablebox object in QlikView production applications. But I do utilize them when I am developing. Tableboxes are a great way to test if your data tables are connecting together appropriately and to see if there are any transformations required to get your data looking good for your users. They can be added quickly to your sheet as they do not require any expressions. Just add the fields you want to visualize and the Tablebox object will show you all the unique combinations of the data.

These Dates are supposed to line up. The tablebox alerts us to the problem.

15 Set up Your Listboxes and Then Copy Sheet

You will likely have listboxes, multiboxes, a current selections box and maybe some branding objects like a company logo on each page. And mostly, you want these items to remain consistent across all your QlikView sheets. Generally, I create this “selection layout” and then right click à copy sheet to create ready-made sheets for all subsequent views. This will save you time AND will keep your layout consistent.

16 Dimensions and Expressions First – Cosmetics Later

When developing front-end charts and visualizations, it can be tempting to go through each of the property tabs and check all the boxes you think you will need. This is fine for experienced developers but you could possibly select options that effect the availability of other options, getting you into a hole that you can’t easily back out of. And because you took all these options at once, it is sometimes difficult to figure which option messed up your visualization. I would instead recommend doing 4 things when creating any visualization before getting into any cosmetics:

  1. Select the visualization type
  2. Add the required dimension(s)
  3. Add the required expression(s)
  4. Select OK and make sure the chart works.

By following these steps, you know that you the fundamental workings of the chart are solid. Then you can go back into the properties and tweak the look of the visualization to your liking.

First, get the chart working.

 

Then you can make it look nice.

17 Put Your Common Expressions into Variables

I often start creating my expression syntax in each object. The expression dialog has the tools that I need to create and test this syntax effectively. But when I have my basic expressions created I will house each of them in a variable so that I can reference a uniform expression definition every place I use it. This will save you time when you have to change that definition because you will only have to update the definition on the variable rather than having to visit each object. You can create these variables in the variable overview Settings –> Variable Overview. You might instead opt to create these variables in the QlikView script with a set function. Both methods will help you maintain common definitions for your metrics.

18 Memorize the Shortcuts

There are a plethora of routine things you will do like initiate the Edit Script module (ctrl-e), the Table Viewer (ctrl-t) or perform a Reload (ctrl-r). All the common tasks in QlikView have a shortcut. Also, keep in mind that all your traditional Windows shortcuts like Copy (ctrl-c) and Paste (ctrl-v) work as well. Get to know the common shortcuts. They will save you clicks! Living Qlik: QlikView Designer Quick Tips and Shortcuts LivingQlik: Top 5 Little Known QlikView Shortcuts

19 Lean on Resources

You cannot do this alone! Thankfully there are many resources online that can assist you in your development learning. Google is your friend.   This article from Quick Intelligence is a great resource for all Qlik Developers looking for a place to go for their specific need. This article links out to resources for many common needs. Quick Intelligence: The Qlik Developers Toolbox   The Qlik Community is awesome. Every conceivable question has been asked and answered here. I leverage to this day. You can view anonymously, but you should register (free) to participate in the community. The Qlik Community   The blogosphere is rich with depth for the Qlik universe. There are over 2 dozen blogs being written by developers all over the world just full of in-depth information on all things QlikView. AskQV helps aggregate these blogs so that you can find the author, blog, article you need. AskQV (Blog Aggregator)

Final Thoughts

Learning a new skill in the BI world is a daunting task for anybody. But it is an exciting task. Hopefully, these tips will help you get on your way. I welcome your suggestions.

Happy Qliking!

9 comments

The Qlik Sense Wishlist Results Analysis

A few weeks ago, I published a post 16 Items on My Qlik Sense Wishlist. It was just a “List to Santa Qlik” on the features that I would like to see in Qlik Sense. This post seemed to resonate with LivingQlik readers. Many people added their own wishlist items or voted on the features from the article they would like to see. This post has definitely garnered the most audience participation LivingQlik has ever seen which is just awesome. 30 post Comments – I know this really isn’t that much, but it is a record for me. 80 Twitter engagements – This is what Twitter Analytics says. Not really sure what it means, lol. 100 LinkedIn shares – Provided by BuzzSumo. Anyway, I thought it would be fun to compile all the comments I received where readers submitted their own wishlist items and analyze the results in a Qlik Sense application, of course! This post is really just a thank you letter to all the great people out there that shared their thoughts and opinions with me.

Resources

I utilized the following extensions for this app: Qlik Branch: Word Cloud Qlik Branch: Color Styler NarrativeScience: Narratives for Qlik Sense

 

Qlik Sense Wishlist Analysis

The data source is just a 2-column spreadsheet. Here is the Qlik Sense application if you want it: Wishlist-Results.qvf

  • The most votes by far was for Dynamic Labels in Qlik Sense. As described in the post, the ability to calculate the title of a metric is nothing short of vital to the success of an application.
  • The idea of adding some sort of Dollar-Sign Expansion Preview was popular as well. It’s one of those things that I took for granted in QlikView until I realized there was not an easy way to test this in Qlik Sense.
  • Surprisingly, the Only One Selected Value feature was not too popular among the readers, although that is a function I rely on in QlikView.

Here is the complete list: Qlik Sense Wishlist Results - Bar Chart   Here is a word cloud of the features that Qlik Sense developers are wishing for.

  • Do word clouds have tremendous analytical value? I don’t think so.
  • Are word clouds really fun to look at? Completely.

Qlik Sense Wishlist Results - Word Cloud
Below is a tree map of the sources for reader engagement. People felt the most natural leaving comments on the post itself. LinkedIn was another strong forum for participation. Twitter is fun, but did not create too many comments. Facebook is a waste of time normally but I still utilize it for some odd reason. Qlik Sense Wishlist - Tree Map by Source   NarrativeScience has a great premium extension that applies natural language analysis to charts in your Qlik Sense applications. It is very impressive technology. I utilized the extension here to do an automatic analysis on the wish counts. It speaks for itself, literally. Qlik Sense Wishlist - Narratives for Qlik Analysis

Final Thoughts

I have submitted this list to somebody at Qlik. Not sure where it will go, but thought you should know. Although running a blog is admittedly time-intensive from an author standpoint, it wouldn’t amount to much if nobody cared to read it. So I sincerely thank you for taking the time to read the posts, and maybe even participate with me in this journey like so many did for the Wishlist post. We all work together to make a better Qlik ecosystem. So thank you again. Happy Qliking!

3 comments
Page 3 of 19
1 2 3 4 5 19

Join us at Living QlikView

Never miss a post.

We Never Spam. NEVER.

other sites

ASKQV One Spot – Many QV Blogs
Visual Data Group My QlikView partner
Official Qlik Community The great Qlik Community
QViewer Handy QVD Viewer

Show Buttons
Hide Buttons