Robot in the Cloud: Automatic File Upload to Qlik Sense Cloud

In my previous post about my company’s migration to Qlik Sense Cloud Business, I described my frustration about having to manually upload data to my company’s workspace. I also expressed my belief that Qlik Sense Cloud would grow as fast as Qlik could develop data connectors for it.  In his comment to the post, I think Aaron Couron summarized this sentiment well.

“I have to extract the data manually from the database into a file (qvd or otherwise) and put it up on the cloud. I guess that is an issue with any cloud vendor where I have on-premise data to feed it, but because BI, in general, relies so heavily on these kinds of resources, it becomes the “Elephant in the Room”.”

At some point, Qlik looks prepared to make this elephant disappear by adding the myriad connectors it acquired from Industrial CodeBox. Since December 2016, you can extract data directly from Salesforce and just last week Michael Tarallo posted a quick preview of the soon-to-be-released REST connector in Qlik Community.  He also purposed a neat way to upload text files from Dropbox through it.

In the meantime, I’ve created a temporary solution to automatically upload data files using a robot. I finished it two weeks ago and it has run well 80% of the time. Barry Harmsen experimented with using a robot in Qlik Sense Cloud to invite users in November 2015. In a comment to the previous post, he shared his experience and remarked about the hassle it was to maintain the robot given how often Qlik Sense Cloud changed its UI flow and even the names of its UI elements. A year and a half later, I assume there are less drastic changes. Over the last 2 weeks, the few issues I’ve had were because my script was in need of some fine tuning and were not due to any change in Qlik Sense Cloud.  Regardless, I’ll keep you up to date on the feasibility of using a robot while we wait for a more permanent solution.

Of course, the permanent solution is using a data connector to automatically refresh data directly in Qlik Sense Cloud. I recommend that you stayed informed about Qlik Sense Cloud updates, and that you swap the robot for a connector when the one you need becomes available.  While we wait, I will do my best to keep the core script that I share in this post up to date in GitHub, and I will also show you how to maintain it yourself.

The steps we are going to take to create the robot are the following.

  1. Set up Selenium Webdriver
  2. Set up AutoIT
  3. Configure the AutoIT and Selenium code to your environment
  4. Create and schedule a Windows Task

Before last week, I had never heard of Selenium or AutoIT, so you don’t need to have any previous experience to go through these steps. It took me about 8 hours to get everything up and running including the time I invested in researching which tools to use and creating the script from scratch. I hope that people can be up and running in less than 4 hours with the help of this guide.

Continue reading “Robot in the Cloud: Automatic File Upload to Qlik Sense Cloud”

Qlik Sense Cloud – Finally Getting Down to Business

Last week I accidentally published this post before going to bed and woke up the next morning with a nice surprise that people had visited the post when it was still supposed to be a draft. I apologize for this leak and plan to catch those brain cells responsible for wasting your time.

As a small business owner, I am a big fan of cloud software.  At this point, I almost have more tabs pinned in my browser than I have shortcuts on my dock.  For example, you can find my project management tool (Jira), my knowledge management tool (Confluence), my file management tool (Dropbox), and my team communication tool (Slack) pinned at the top of my browser. As such, I’m excited to see Qlik Sense Cloud approaching the critical moment when it will be a veritable competitor of Qlik Sense on-premise.

Over the past two years, I’ve kept an eye on Qlik Sense Cloud. Qlik employee Michael Tarallo sums up these years and what the future may hold in his blog post Keep Your Head in the Clouds.  Well-known Qlik enthusiasts Stephen Redmond (Low down On Qlik Cloud 2.0) and Steve Dark (Qlik Sense Cloud New FeaturesQlik Sense Cloud Plus Is Here) have also shared the milestones it has reached in their blogs.

According to Michael’s post, Qlik Sense Cloud has almost 100,000 users in 170 countries, but I think it would be fair to ask him how many of those users use Qlik Sense Cloud on regular basis. It may be true that they are active users, but if I were to make a bet, I would wager that many of them acted as I had over the past two years.  They created an account, played around for an hour, and only returned when there was an announcement to see what was new. Even though 100,000 users may not be an accurate measure of Qlik Sense Cloud usage, the measure does speak for the initial interest many have had in what it offers. It also represents an opportunity for Qlik to convert them into paying customers once Qlik Sense Cloud adds more features and matures.

Released in December 2016, Qlik Sense Cloud Business is Qlik’s first serious attempt to provide a cloud service for businesses. In all fairness, there are a few kinks that need to be worked out and a few features that need to be added before I would consider it ready for most users. However, my overall opinion is that it’s a great start, and as an early adopter, it finally convinced me to use it internally in my company.

Continue reading “Qlik Sense Cloud – Finally Getting Down to Business”

Exponential Distributions in Qlik

Last week, I gave the business case for using an exponential distribution to predict a customer’s purchase frequency and detect at-risk and lost customers, Sales Analytics in Qlik: From the Basics to Statistical Modeling. In this week’s post, we go over the details of calculating and visualizing the exponential distribution in QlikView using the following chart.  I also add some final thoughts on how to use the information it tells us in a real-world sales dashboard.

Continue reading “Exponential Distributions in Qlik”

Sales Analytics in Qlik: From the Basics to Statistical Modeling

The basics

The most common Qlik application involves sales data analysis. Period.

Well, I don’t have enough information to back that up, and since data analysis is my life, I can’t make unsupported claims without some major nervous facial twitching (or so my wife says). However, I would bet based on personal experience that it is one of the most common applications, and moreover, I’d go as far to say that it is often the first analytical application that businesses develop after buying Qlik.

One the most obvious reasons that this could be true is that sales data is huge, low-hanging fruit. Sales is what drives most businesses and the data trail it leaves is usually the most readily available data to analyze. When a company purchases Qlik, it is often after continuous investments in an ERP (Enterprise Resource Planning) system, a CRM (Customer Relationship Management) systems, a customer portal and/or numerous Excel reports – all of which make sales data ripe for harvest.

Many of you are probably familiar with the following sales metrics.

MetricDefinition
Gross Sales RevenueSales before discounts measured in monetary units
Net Sales RevenueSales after discounts measured in monetary units
Sales VolumeSales measured in non-monetary units such as an individual item, boxes, pallets, kilograms, tons, etc.
Unit SalesAlso referred to as Average Price, it can be defined as Net Sales Revenue divided by Sales Volume
HitsNumber of sales transactions or invoices
Gross ProfitNet Sales Revenue minus the Cost of Goods Sold (COGS)
Gross Profit MarginGross Profit divided by Net Sales Revenue

If you are familiar with these metrics, you’ll also be well acquainted with the series of dimensions that often slice and dice them.  Catalogs that describe customers, sales representatives, products, dates, branches, stores, promotion codes, and channels answer the questions of who, what, when, where, why, and how that surround the sales numbers.  You’ll also recognize the importance of using some type of reference data that comes in the form of a budget, a forecast, or at the very least, historical data.

Almost immediately after businesses start to take advantage of this basic, yet powerful, sales data analysis, they start to adjust their business questions. We can answer some new questions by adding a new metric, a new dimension, or a new visualization, but some new questions involve more advanced analysis techniques.  One of the most popular questions that I encounter and that requires a more sophisticated approach is the evaluation of customer retention and the detection of customers that the business is endanger of losing.  For here on, we’ll refer to this type of analysis as customer churn.

Continue reading “Sales Analytics in Qlik: From the Basics to Statistical Modeling”

Let the Chain of Inpirations Continue

As Qlik Sense comes of age, I anxiously wait for the day when we will talk about the good ol’ QlikView days when we used to make map charts using a background image and a scatterplot chart.  In the meantime, I’ve been finishing up one last, great QlikView adventure with Mastering QlikView Data Visualization and seeing how far ol’ QlikView can still go.  Although, we have not seen a major update to its native visualizations in 5 years, I’ve been pleasantly surprised that there are still a few new tricks to be learned and boundaries to be pushed.

Stephen Redmond was the first to make a cookbook on tips and tricks, and he inspired us to look for ways to squeeze the most out of QlikView. More recently, I was inspired by a QlikFix blog post Barry Harmsen wrote on macros to create a my own macro that generates design layout grids and I called it the QlikView Grid System Tool. In turn, Barry was inspired to create a improved GridMaker. (Yes, he even improved upon the name.)

That, and to add insult to injury, he has a uncanny knack for making the most hilarious memes.  I’ll leave the meme business to my fellow consultant Qlik Freak Julian Villafuerte, but as far as QlikView is concerned, I thought I’d return the favor and continue the chain of inspiration.

While writing my book, I was recently inspired by an excellent QlikFix blog post written by Frédérique Verhagen about creating bar chart target lines in QlikView.  This post was the piece of the puzzle I was missing to finally create a single object, native bullet graph in QlikView.  If you convert Frédérique’s bar chart into a combo chart with stacked bars, an error bar and a stock chart expression then you have a native bullet graph that is as robust as any other normal QlikView chart.

BulletGraph

By the way, you can learn the trick on how to add a stock chart expression in Stephen Redmond’s cookbook.  I’ve left the rest of the details in my book because otherwise Packt will blame me for plagiarizing.  No kidding, they’ve already done it once.  I bet some of you can figure it out by yourselves, so I’ll also keep the rest of the tricks for the book.

And of course, the bullet graph isn’t perfect.  It would be impeccable if only we could change the line width of the stock expression.  If you like the idea then vote to add this feature in this Qlik Community idea. Though, in all honesty, what I’m really hoping to do is to inspire one of you to take it that little bit further.

Karl

Data Visualization for Accounting and Unicorns

Recently, I’ve been reviewing the financial analysis part of my new book and I’m reminded how hard it is to create data visualization for an area obsessed with calculating every amount to the exact cent.  When we develop our visual analysis it is so easy to get trapped in this labyrinth of detailed tables and numbers.  We hit our heads against the wall and try to imagine one omniscient visualization that captures every detail and we only succeed in creating something more impenetrable than the table we were trying to replace.  So, let’s step back and think about what we need to do to create a successful visual analysis for an accounting department. Continue reading “Data Visualization for Accounting and Unicorns”

Data Discovery in QlikView – Part 2 – Easier Data Integration and Modeling

Qlik Sense and QlikView are two data discovery tools that make it easy to go from raw data to data visualization.  This in contrast to Tableau that is limited to combining data tables with joins.  If your data source is, for example, an OLTP system that contains a long list of table with complex relationships, you will probably have to invest time to transform and model the data in another tool before you can use Tableau for data discovery.

The couple times I tried to implement Tableau for a company, I had to work all night in SQL Server Express to create a data model that made Tableau easy to use.  It was this experience that made me realize that data discovery does not depend on great data visualization alone.  It also depends on being able to easily extract, transform, and model data.

Continue reading “Data Discovery in QlikView – Part 2 – Easier Data Integration and Modeling”