Pivot Tables in Qlik Sense

Qlik Sense

by Jason Green on 2014/07/29

Qlik released their latest data discovery and visualization tool, Qlik Sense, last week, and I’ve been putting it through the paces. Overall, I’m impressed. Qlik Sense competes directly with Tableau and I believe is the better of the two tools.

QlikSense Charts

The most glaring omission I see so far is the inability to create a pivot table (aka: crosstab). I don’t know if it’s on principle that Qlik doesn’t believe you should be creating pivot tables on a BI dashboard, but it’s a feature that I think must be added. There are probably some visualizations that are more appropriate than pivot tables, but the inability to toss a quick summary below one of my charts is just maddening. Of course you can still create a regular table, but each permutation of the data is then repeated as a new row.

That said, I’m writing this post both as a plea for Qlik to add pivot tables to Sense and as a call for hacks. Have you found any tricks for creating a pivot table within Qlik Sense? If so, please let us know! I’ll also post updates on this post as I receive them.

{ 0 comments }

I frequently create reports that show the average (mean) value of a metric based on any number of dimensions. For example, I might report the average cost of a product by state. Usually, I include the overall average cost at the bottom of the report, and one of the first things people do to “check” my work is to highlight the entire row of average costs, to have Excel show them the average of the entire group. These numbers rarely match because taking an average of averages is wrong.

SumProduct

The reason an average of averages is wrong is that it doesn’t take into account how many units went into each average. For example: Let’s say we only have orders in 2 states, New York and Pennsylvania. The average cost of the product in NY is $100, and the average cost in PA is $50. Now…let’s say order volume in NY is 10 orders, and volume in PA is 2 orders. Taking the average of the two averages would give us $75 as the overall average cost. (($100+$50 = $150)/2) = $75 However, if we take volume into account, as we should, the average is $91.67. 10 NY orders at $100 = $1,000 + 2 PA orders at $50 = $100, for a total cost of $1,100, divided by all 12 orders = $91.67. That’s a difference of over $16! Now what if we had 100,000 vs 20,000 orders and we were trying to budget or plan for next year? Instead of reporting the actual total costs of $11,400,000, we would inaccurately report that our total costs are expected to be around $9 Million. Finding out late next year that you miscalculated by $2.4 Million might be considered a big deal…

If you must calculate an average of these averages, and you have volume information, there’s a handy Excel function called sumproduct. SumProduct is a function that takes two arrays (vectors) and multiplies them together, line by line, before adding up the total. In the example above, assuming our state is in column A, Volume is in Column B, and Avg Cost in Column C, we’d use the formula =sumproduct(B2:B3,C2:C3). This takes B2*C2 + B3*C3, giving us the total of $1,100, as we had calculated previously. If we take that formula a step further, to divide by the total number of orders, we can get the actual average cost per order. =sumproduct(B2:B3,C2:C3)/sum(B2:B3)

So, the next time you need to get the average of values that have already been averaged, make sure you take the volume into account.

{ 0 comments }

How to Create a Simple Graph With R

July 1, 2013

In the process of doing an ROI analysis, I wanted a simple area chart showing the negative burn down of our costs while the project was in development, through to the positive savings of the project after completion. Excel didn’t do a great job for me, so I thought I’d give R a try. As […]

Onward to the full article →

Outlandish Measurements

June 28, 2013
Thumbnail image for Outlandish Measurements

So I’ve been reading “How to Measure Anything” by Douglas W. Hubbard and “Predictive Analytics” by Eric Siegel, and decided to create a post highlighting things that are “impossible,” or at least difficult, to measure. Then, I’ll take on the task of finding ways to measure them. Not everything has to be business-related either. Maybe […]

Onward to the full article →

Is Google Analytics a Waste of Time?

February 28, 2011

Yes, if you only look at the data and don’t act. Who cares if your bounce rate is 70% or 30%? Who cares if you have 5,000 visitors per month? Who cares if 30% of your visits come from California? None of that matters if you just look at it and say, “Yay! I have […]

Onward to the full article →

Google Analytics Referrer Spam

January 26, 2011

If you’ve launched a new website recently and were excited to see referrals from golbnet or forexmarket, you have been spammed. This is a tactic used by spammers to get webmasters, curious to research their referrers, to visit the desired website. Also referred to as log spam or referrer bombing. It’s not necessarily malicious, but […]

Onward to the full article →

What is My Conversion Rate?

January 24, 2011

If you’re just getting started with internet marketing, your first question might be, “What is a conversion rate?” Put simply, your conversion rate is the percentage of visitors who complete your desired action. If your goal is for your visitors to buy your product, a conversion would occur once that has happened. If you had […]

Onward to the full article →

Welcome to Convalytics.com

January 22, 2011

Convalytics.com was originally created to focus on the analytics and statistics behind marketing conversions.  I’d like to take that a step further and devote this website to the demonstration and discussion of all types of analytics. I will be writing about data visualization, the latest programming languages, and the latest business intelligence applications. Specifically, QlikView, […]

Onward to the full article →