The Most Important Formula in Klipfolio

New Users often get frustrated at not being able to build the Klip or formula they have in mind in Klipfolio. They see how simple our templates are, and feel that building a dashboard from scratch should be just as simple.

It generally isn’t. Spiderman’s uncle famously quipped that “with great power comes great responsibility.” We say that there is a bit of a learning curve.

If you’re just starting out, or if you’re a long time user looking for a quick refresher, there are four Functions in Klipfolio that you should look at, and master early on: SLICE, SELECT, GROUP/GROUPBY, and LOOKUP.

These Data Manipulation Functions come together into what I will controversially call the single most important formula in Klipfolio.

Here it is:

Data source that it uses:

Klip that it built:

The LOOKUP function

The most important piece that has helped me master this, and all of our functions, is the understanding that each function has a certain number of individual sections, called “parameters.” These parameters expect a certain kind of data, and perform a certain action. Take LOOKUP, for example, and look at its 3 parameters broken out in this formula:

LOOKUP

Parameter One:

This is a “Reference” (more on this later) to the X Axis of the Klip, containing various States in the US. Think of it as a long list of values.

Parameter Two:

Think of this as a 2nd list of values. This time we’re not looking at all of the States (B:B) on our X Axis, but rather, we’re looking only at those with an Order (F:F) that is Delayed.

Parameter Three:

Here, we’re looking at the States (B:B) that have Delayed Orders (F:F), and totalling the value (E:E) of these delayed orders.

The LOOKUP function takes a list of values (First Parameter), looks for matches in a 2nd list of values (Second Parameter), and, where there is a match, returns a corresponding data set (Third Parameter). The reason LOOKUP is used here is that of the 20 States on the X Axis, only 9 have Delayed Orders. We therefore use LOOKUP to align the numerical data from the Series with the correct State on the X Axis.

Let’s take a closer look at each Parameter.

Parameter One

When using LOOKUP to align data to the X Axis, Parameter One is very often simply a reference to the X Axis values. This gives us all of the X Axis data, and prepares us to align the numerical data to the correct points. You can use a Reference (3rd button on the formula toolbar,REF) to re-use a formula you built elsewhere in the klip. It is a time-saver, however it’s not a performance saver. Here, when we reference the X Axis, the entire formula is recalculated.

Parameter Two

Think of this as a 2nd list of values. This time we’re not looking at all of the States (B:B) on our X Axis, but rather, we’re looking only at those with an Order (F:F) that is Delayed.

Parameter Two introduces some complexity. We need this data to return matches to at least some of the items on the X Axis: States. We only want to see the States with Delayed Orders, so we use SELECT to specify our simple condition: “F:F = Delayed.” If we only use this SELECT statement, and leave out the GROUP, we’re left with this data:

We see the 42 items in our data source where the Orders are Delayed. But we have many repeating values.

LOOKUP will look for matches in this list, but as soon as it finds a match, it will stop looking. Therefore when it arrived at the first record for “Washington,” it would identify a match with an X Axis data point, and move on to the third parameter, the value for this Washington order. It would then ignore the 4 other matches for Washington, and move on to the next state.

This is why we use GROUP

With our list of States with Delayed orders GROUP’ed to remove the duplicates, LOOKUP can move through each to identify a match.

Which brings us to Parameter Three, where everything comes together.

Parameter Three

We started with a reference to our X Axis (all States), looked for matches in a smaller set of values that met a certain condition (some States), and where a match was found, this is the formula that will execute.

GROUPBY allows us to perform calculations on GROUP’ed data. What we want to do is calculate the value of the delayed orders totaled by State where delayed orders exist.

Here, again, we’ll pay attention to the number of parameters that a specific function uses. GROUPBY uses 3: values, measure, and method.

The first parameter in GROUPBY is the data we are grouping. This is a hard and fast rule, and often you can simply copy the formula from an x axis, or another part of your Klip, and paste it here. That’s exactly what I did.

If you look at our complete formula again, notice that the GROUP function is identical to the First Parameter of the GROUPBY function (red underlines). We don’t include GROUP inside of the GROUPBY (since that calculation is done automatically) however the entire contents of the GROUP are re-used.

The Second Parameter of the GROUPBY is very similar, so I almost always copy/paste this in as well. It is also a SELECT statement, and the condition is the same.

Let’s remember the three Parameters of GROUPBY: (data you are grouping) (data that is being calculated) (type of calculation - this can be left blank, and the data will be SUM’ed by default).

We’ve finished the first part of the GROUPBY. For the 2nd, we want to SELECT the data from E:E (# units) where F:F (order status) is delayed. The SELECT statement is identical, but we swap B:B (from the first part of the GROUPBY) for E:E.

Understanding the LOOKUP formula

This formula looks complex, but when you understand a) the number of parameters inside each function, and b) what each parameter needs and does, you can break a long formula down into smaller, manageable pieces.

From experience helping our customers and building dashboards for our own use (including the Sales Dashboard that hangs proudly on our wall), I can tell you that this is the formula I encounter most. Mastering it will give you a powerful, versatile tool in the toolbag.

Happy Dashboarding,
Zach Kathnelson