Quick Set Analysis Tip – Selecting Current Selections

Wow! I think it’s about time to post something new since the post about Packt has been the latest post for two months now.  I’ve been quite busy lately trying to launch new initiatives in the company and I’ve recently decided to write a follow-up book about more advanced QlikView data visualization and analysis.  If you have any requests on what you would like to see in this new book, please send me your comments

As I start investigating the topics and writing, I occasionally stumble upon tips that I’ve forgotten about.  Have you ever done something like the following to respect the current selection of a field in set analysis while ignoring the selections in other fields?

=num(sum({1<[MasterCalendar Year]={$(=concat([MasterCalendar Year],’,’))}>} Amount),’#,##0′)

Continue reading “Quick Set Analysis Tip – Selecting Current Selections”

Great Opportunity to Stock Up on Qlik Books from Packt Publishing

Packt Publishing is celebrating its 2000th title, so if you were waiting for a reason to buy all those Qlik books from Packt Publishing, here’s a great opportunity to buy one and get another one free.

Of course, there’s my book Learning QlikView Data Visualization and I’m going to take this opportunity to buy QlikView Server and Publisher and QlikView Scripting, which are the two Qlik books I’m missing from my library.  If you’re one of the few that doesn’t have the now classic QlikView 11 for Developers or QlikView Developers Cookbook then this is an offer you shouldn’t let go by.

A couple years ago there were no books about Qlik.  I don’t know the story behind why Packt decided to publish all these QlikView books, but thanks to them and a couple other publishers Qlik now has a growing list of books to choose from.  I hope this is just the start.

Act fast though.  The offer is good until March 26th.


Advanced Functions – Exhaustive DSO using dollar-sign expansion with parameters

As everybody probably knows by now, the book “QlikView 11 for Developers” by MIguel Garcia and Barry Harmsen is an excellent resource.  Other than the cover photo and the title that could lead someone to think developing Qlik involves hardcore programming; and the space wasted on showing the pie, grid, and funnel chart, I enjoyed reading what is to date the best didactic material about Qlik.

Even though I’ve worked with Qlik for eight years, I learned a few tidbits from the book.  For example, I hadn’t noticed that you could add parameters to variables, and with the help of dollar-sign expansion, pass parameters to those variables.

I avoid using techniques in Qlik that make it feel like you’re programming because I believe Qlik should be as simple and as open as possible for business users.  However, sometimes the benefit of using advanced techniques is too great to ignore.

Continue reading “Advanced Functions – Exhaustive DSO using dollar-sign expansion with parameters”

Set Analysis – New and Lost Customers with Nested Advanced Search

I love the analysis that can be done using Qlik’s Set Analysis, but its syntax leaves too many business users lost and confused.  Those same business users that use Excel today for their analytical needs and aren’t completely convinced that Qlik is accessible enough to perform their own custom analysis.

I believe that one day applying simple and complex business rules will be made easier, but until then, I’m going to share tips and tricks to keep Set Analysis syntax as simple as possible.  Additionally, I will use examples of commonly used indicators that you can re-use for your own Qlik applications.

One of the confusing attributes of Set Analysis syntax is knowing when to use double quotes ( ” ) and when to use single quotes ( ‘ ).  According to Qlik Help (a great resource),

A search is always defined by the use of double quotes, e.g. <Ingredient = {"*Garlic*"}> 
will select all ingredients including the string ’garlic’.

A search can also be based on a formula or a range.  For example, <Customer = {“=sum(Sales)>10000”}> will select all customers that have a sum of sales greater than ten thousand and <Year={“>=2008<=2010”}> will select all including between and including 2008 and 2010.

Continue reading “Set Analysis – New and Lost Customers with Nested Advanced Search”

Advanced Scripting – Creating an e-mailing list in Qlik

Recently, I set upon creating an e-mail list for a bi-monthly newsletter that my company Evolcon is going to send with updates on everythings related to Qlik and becoming a more data-driven organization.  Of course, I used Qlik to parse and clean the e-mail list, and in the process, I used some not so common scripting functions that are fun to use.

The first step was to extract the e-mail information from Outlook and Gmail.  You can easily find sites that will lead you through the process in Google.  I used a Google script found on this blog and the Outlook extraction was pretty straight forward.

Parsing with subfield()

Once I extracted all the e-mail addresses from my e-mail and copied them to an Excel file, I started to write my script in QlikView to parse and clean the e-mails.  The first challenge I encountered was parsing the domain from the e-mail.  That’s a pretty simple job with the subfield() function, and the following function did the job:

SubField(Email,'@',2) as Domain,

Continue reading “Advanced Scripting – Creating an e-mailing list in Qlik”

Thoughts on being a Qlik Luminary

2014 Qlik Luminary Digital Badge

Life is made up of moments, and I remember clearly the moment I asked by former boss for a career change. Eight years ago I decided to leave a future of implementing ERPs and bet on a new future helping people analyze their data with a software called QlikView.

In 2006, QlikView was new to Mexico and it wasn’t easy to convince prospects that we were better than the OLAP-based competitors that had already been around for decades. However, we knew it was the product of the future because almost every time we competed with OLAP-based software in Seeing is Believing events, we would leave the prospect in awe and win the project. I’ll never forget the time I made a business user shed tears of joy after seeing an analysis in QlikView that the company had never been able to perform after years of numerous, futile attempts with other tools.

I’m more an analyst than a salesperson, so I know QlikView is not perfect. I respect what Stephen Few has done for data visualization and I’ll always have a place in my heart reserved for Tableau, but as I commented in my recent book, I believe QlikView is the best all-around data discovery software. There’s more to data discovery than data visualization. Rapid, efficient and easy data extraction and transformation along with an innovative associative data model are all vital tools to discover data, and this is why I still prefer QlikView.

Now eight after first choosing QlikView, it has been renamed Qlik and as a new, game-changing version is ever closer to being unveiled, I’m going to bet again on Qlik. I’m honored to be among a group of the Qlik Luminaries that includes an impressive list of customers, partners and enthusiasts. As a Qlik Luminary, I promise to take on the responsibility to share how great Qlik is to all those who still suffer from information deficiency, and I also promise to represent the needs of my customers to make Qlik an even greater data discovery tool.

I will keep you updated on what I can share with you during what looks to be an excellent 2014.

Let’s do it again! Win a Free Copy of Packt’s Learning QlikView Data Visualization.

Packt Publishing is going to be giving away 2 free copies of Learning QlikView Data Visualization this week.


Overview of Learning QlikView Data Visualization

  • Explore the basics of data discovery with QlikView
  • Perform rank, trend, multivariate, distribution, correlation, geographical, and what-if analysis
  • Deploy data visualization best practices for bar, line, scatterplot, heat map, tables, histogram, box plot, and geographical charts

How to Participate
During the
last giveaway, I asked for ideas on what data visualizations you would like to add to QlikView.  Now, I can’t imagine the development team at QlikTech being able to focus on creating and improving every chart and graph in QlikView, so let’s help them focus on the best data visualization techniques, and therefore leave behind some of the charts and graphs in QlikView that don’t do a good job of helping us discover data.

Continue reading “Let’s do it again! Win a Free Copy of Packt’s Learning QlikView Data Visualization.”