Calculations Guide Book

20 Sep 2016

NEW BOOK: Working with Data - All the Calculations you Need to Know

Ben Marshall

What is this book?

I make one Excel mistake per day. I've been using it for years, and there are some things that I use so often I can (literally) do them with my eyes closed. But the syntax doesn't always follow consistent patterns. And I use tools other than Excel. Like Alteryx... and OrgVue... and Tableau - a lot. There are probably about 30 calculations I consistently use as a data analyst and consultant, but each is written differently, with different default behaviour in half a dozen software tools. 180+ calculations is a lot for experienced employees, and even more to new joiners to the business.

That's where the idea for this book originated. Some colleagues and I wanted a reference resource that we couldn't find anywhere else. What you're reading is our own guide book; a comprehensive list of what we consider to be the most important functions to know - the ones that will get you through 80% of what you need to do as an analyst, consultant or data scientist. As well as that, we show you how to actually write them in the six main tools we use:

  • Excel - the ubiquitous spreadsheet used by almost every business
  • Tableau - a drag and drop visualisation tool for business intelligence
  • Alteryx - an easy to use platform for preparing, blending and analysing data
  • OrgVue - a leading solution for data-driven organisational design
  • tSQL - the primary language used for interacting with Microsoft SQL Server
  • Python - a widespread programming language very useful in data science 

Working With Data - All the Calculations you Need Ebook

Why did we write it?

As a Company and a group of authors, we work with data a lot. You can probably boil down what we do on a project to working out one of two things:

a. What data (and data structure) will provide the answers a client needs; and

b. How to handle that data in a way that is efficient, repeatable, auditable and collaborative.

If you want to be a successful consultant, analyst, business partner, or data scientist, point (b) is vital. Some of it will come from knowing how to use the right tool for the job, in the right way, some from getting your basic calculations perfected. But with more data, more tools, and more pressure, that isn't easy.


Information is changing business. Information is changing full stop. Whether it's the personalised vouchers at the supermarket self-checkout or the wearable tech on your wrist, data is now more available more quickly than ever before. For businesses, this means more and better opportunities to understand your customers, make good use of your workforce and look for new opportunities. But when handling large amounts of data (especially from multiple sources or on a repeated basis), human error is much more likely.

New rewards bring new risks. For the first time, there is enough technology on the market that your options aren't limited to Excel or a bulky enterprise implementation. When Gartner's magic quadrant of analytics tools to watch came out this year, they updated their criteria to give extra weight to agile tools for self-service analytics. The clear industry direction is about moving key business analytics away from the central IT function and into the hands of business users, so any data manipulation or analysis needs to be clearer, more auditable and more open to collaboration than it has before.

"Give me results." There is also more pressure on business executives to make evidence-based decisions. Since the financial crash, uncontrolled spending and 'blue sky' consulting projects have fallen out of favour. Now everything is about tangible results. For managers - knowing what questions you need to answer to drive real business insights and improve performance; as someone working directly with data - knowing how to source, process, analyse, and visualise it in a way that adds value without the late nights!

How can you use it?

The book is split into five main sections. The first three (Strings, Numbers and Dates) correspond to different types of data, and the last two contain types of operations, either Logical calculations or more General options which don't depend on the data type (e.g. filtering).

We've tried to make the content as clear and user-friendly as possible - comprehensive enough for graduates joining a business, but clear enough to be a useful reference for old hands. On each page, you'll find described a specific calculation, and in a section for each tool we have provided:

  • Model syntax
  • One or more Examples of the syntax in action
  • A collection of User Notes where we think the syntax in question is difficult or extensive, or if a tool has unusual or default behaviour.

GitBook Preview

After reading it, you should get an understanding not only of the calculations themselves but how they fit within each tool; how the tools differ but also how they are similar. Just like learning a language, you'll get a taste for how each one behaves, notice similarities in wording and structure, and get better the more you practice. 

A personal note

Various people contributed to this book, from business analysts and developers to sales directors and financial managers. We all know the pain of wrestling with data and the satisfaction of not having to do something again and again or finding the small error which is making your life hell. That's why we've made this book public and free to download. It's not a panacea; you might not find the answers to all your questions, but everyone involved in creating content learnt something new along the way. Hopefully, you do too.

We'd like to thank the following people who helped make this resource a reality: Will for his technical instinct; Anna and Jakub for their lovely designs; El for her patient and practical guidance; Robbie and Christian for their knowledgeable comments; Miranda, Darshan and Dalia for their diligent proofreading; Patrick, Bill, and Ben for their valuable suggestions; and Laryssa, who pioneered the launch. You guys rock.

Working With Data - All the Calculations you Need Ebook


Ben Marshall

About the author

Ben is a Consultant at Concentra. He uses a variety of tools, including OrgVue, Tableau and Alteryx to help companies make better use of their data and drive insight. He has a particular focus on healthcare and financial services sectors and has run multiple projects in Organisation Design, HR Analytics, and Performance Management. Ben is a Tableau Certified Associate and one of our software trainers, and blogs about data, visualisation, Tableau and OrgVue.

Related to NEW BOOK: Working with Data - All the Calculations you Need to Know