header bg


The Blog where QlikView and Excel Meet


by Darrenb 13. July 2011 16:23

This week sees the release of the final Harry Potter film, where no doubt Harry will do battle with “He-Who-Must-Not-Be-Named” (please don’t tell me the ending!). For anyone who hasn’t seen or read any Potter, “He-Who-Must-Not-Be-Named” is the bad guy. He’s not just bad, he’s so evil that other witches and wizards are afraid to utter his real name and so refer to him as “He-Who-Must-Not-Be-Named”.

And so the Business Intelligence World has its equivalent with “That-Which-Must-Not-Be-Named”, otherwise known as “Excel”. Mention “Excel” in the wrong company and you’ll feel yourself being put on to the Naughty Step.

But why? I mean, there appears to be a need for it.

Customers Want It

We often hear from QlikView partners that they have a prospect and they like QlikView – “its great, they love it, but…the finance team would like their reports in Excel!”. Although it’s possible to ‘export’ objects from QlikView to Excel, it’s not perfect and only lends itself to snap-shot data and so doesn't meet the needs of the finance team.

It doesn’t always have to be a show-stopper that proper QlikView-Excel integration isn’t there, but to not have it gives other vendors such as Cognos, who do, a competitive advantage. We also hear that some sales opportunities have actually been lost because of the lack of Excel integration.

That scenario seems far from ideal!

In most cases the reports that can be produced in QlikView are perfectly adequate, so why do people still want to use Excel? I mean there are so many issues with it, aren’t there?

The list of “why Excel shouldn’t be used” would be extensive but here are a few of the more common reasons:

  • x% spreadsheets contain errors
  • Data is at a point in time
  • Lack of centralised control
  • Multiple versions of the truth can exist
  • Reports can sit in many people’s inboxes and become dated

Why is Excel used so much?

MS Office is the front end for many people as they go about their daily jobs. They’ve grown up with Excel, it’s familiar to them and they can use it at their level (from novice to advanced) however they want. Companies have also invested heavily in Office and so are happy to get the most out of their investment.

Also, if a report is emailed to somebody outside of the organisation, the chances are they’ll have Excel (or an equivalent) so that they can open it and work with it.

OK, but why do the finance team need it? Why are they so special?

Well, from my own experience as a management accountant (some years ago), we had our monthly reporting, you know income statements, balance sheets, production analysis and so on.  We were part of a group and had to produce financial statements with a very specific layout, which the whole group produced and were consolidated at a higher level, which Excel lends itself perfectly for. Of course, the reports were given a little 'massage' in places to make them more 'head office friendly' and this ideally needed to be done outside of the live data, so in stepped Excel.

Although we had highly systemised procedures in generating these reports, on analysing them, I’d find things that didn’t look right. So, I’d then have to quickly do some further analysis to try and explain the anomaly. This would usually mean combining information from several sources; that could be from other spreadsheets, emails or telephone conversations. At that point I’d have no idea how the final report will look or if I’d get all the information that I needed. So, because my reports had to be flexible and my customer needed them now, I’d turn to Excel.

After the mayhem joy of month-end, we’d get various requests for information relating to pretty much any aspect of the business. Sometimes this was just a case of pointing them to the correct reports, but more often than not it was a bespoke report, so Excel was the tool of choice.

What’s more, businesses don’t stay still, they are constantly evolving and so too does the consumption of information. Since I was an accountant the amount of information that we consume as businesses has grown considerably, so I can only imagine the difficulties that some reporting needs now pose.

And that’s part of the life of the management accountant – meeting customer’s needs so that they can make the right, informed decision in a timely manner.

As you can probably see, Excel plays a major part in satisfying those requests for information. However, I must stress that where ever possible reports should be generated in a controlled way and not in Excel. Excel must be respected as an aide to reporting and only used by people with the right skills, in the right circumstances.

Why use add-ins?

Given that it's used so much, it seems only sensible that Excel should be part of the reporting environment and if it’s embraced by the IT department, then its use can be controlled. By shunning Excel, its use is just driven underground and people will find ways to use it, resorting to time consuming methods such as “copy and paste” or manually typing in the values, which both pose numerous risks.

Many of the issues of using Excel can be overcome by using a proper integrated add-in. An add-in allows for a controlled way to get data out of the QlikView application, by giving the Excel user access to the data model and security policies that are already defined. What’s more, using an add-in also extends the existing Business Intelligence platform and it’s investment to more users.

Some BI vendors have proper Excel add-in integration, but QlikView doesn’t. However, with QVExcel you can have proper QlikView-Excel integration.

In the case of QVExcel, you can use data from QlikView in a few ways, one of which is using Excel-like formulas which creates a live link from a cell in Excel to the QlikView data model. This means that the report will always reflect the latest data and make use of the business definitions that are already in the application, which vastly reduces the risk of errors in Excel. Also, being able to place QlikView data into any cell in a spreadsheet also opens up the possibility of creating 'Pixel Perfect' reports - that's right, those income statements with tricky layouts can be produced!

So, the analysts can use the Business Intelligence front end (QlikView) and the Finance team can use their favourite tool (Excel), but they can all work from the same data i.e. a single version of the truth.

That sounds like a win-win for everyone!

So if you’re a QlikView partner, why even risk losing a QlikView sale, give another vendor a competitive advantage or not fully meet your customer's needs?

If you’re a QlikView end user, why go through the pain of copy and paste from QlikView or using reports that don’t meet your customer’s ever changing needs?

If you’re in the IT team managing the QlikView deployment and reporting, why not give your customers the tools that they need, in a controlled way, to satisfy all their reporting requirements?

With QVExcel, you are able to achieve easily and consistently, professional reports that are not possible or painful to create otherwise and therefore improve and protect your existing investments in QlikView and MS Office.

Everyone's a winner, so why not take a look!

Welcome to the New QVExcel Blog

by Darrenb 20. June 2011 17:09

Welcome to the new QVExcel blog!

This is the place to come and find out about all things QVExcel from reporting tips and tricks to new features, sample code and much more.

We'll also be keeping a look out for QlikView related articles and news that we hope you'll find interesting.

header bg