Calculating percent of previous and percent change with table calculations


#1

Table calculations enable you to create calculations that operate on the table data returned by a SQL query. This is great for calculating metrics like percent of previous and percent change. This post will show how to calculate these metrics both down columns and across rows.

Calculating down columns using offset()

The offest() function allows you to refer to other rows in the same column that you are operating in. This is perfect for percent of previous and percent change down columns.

Let’s say you have a table like so:

Percent of previous

In order to calculate the percent of previous of Orders Count, you can make a table calculation like this:

${orders.count} / offset(${orders.count}, -1)

This will divide each value in the table by the value in the row above it.

Percent change

Percent change can be calculated similarly to percent of previous, except you want to subtract the entire calculation by 1:

(${orders.count} / offset(${orders.count}, -1)) - 1

These two metrics will yield the following results:

Using Value Format: #0.0% will format these values nicely for you. Read more about using value format with table calculations here.

Calculating across rows using pivot_offset()

The pivot_offset() function allows you to refer to different pivoted rows in a calculation. In this case, we will want to use pivot_offset() to refer to the previous column (rather than the previous row as in the example above).

Let’s say we have this table:

Percent of Previous

In order to calculate percent of previous across the rows, we can use pivot_offset() in the same way we used offset() above:

${orders.count} / pivot_offset(${orders.count}, -1)

This will divide each value in the table by the value one row to the left. We get the following table:

Percent Change

Again, we can use pivot_offset() the same way we used offset() when calculating down columns:

(${orders.count} / pivot_offset(${orders.count}, -1)) - 1

This is the same as percent of previous, just subtracted by 1. This gives us these results:


Show Percent Change instead of (or in addition to) Show Percent Previous
Filtering in Visualizations using Table Calculations (3.28+)
(Charlie Hsu) #2

Table calculations have been great for us in this exact use case, but we are curious if there is some way to sort by table calculations? (for example - sorting a set of customers by largest percent change).


#3

Hey Charlie - it isn’t currently possible to sort table calculations, but I will pass this on to our product team! Table Calculations are still in beta, so we’re working on improvements! Feedback is great.


#4

I order my pivot columns descending and wish to calculate percentage increase from right to left. If I use -1 as the offset in the pivot_offset function, the difference is calculated from the column on the left. So I use 1 as the offset, and it works fine.

But this look is in a dashboard also — and there, the difference is calculated from the column on the left whether I use 1 or -1 as the offset. I don’t know of any way to get the difference calculated from the column on the right. This appears to be a bug that should be fixed (pinging @lindsey); meanwhile, does anyone know a workaround (besides, of course, the obvious one of ordering my columns ascending)?

Edit: It gets stranger. If I click the pivoted columns’ row header to reorder my pivoted columns’ ascending and then click it again to reorder them descending again, the differences show correctly.


How does pivot_index account for pivot sort order?
#5

Hey Michael - I apologize for the delay. I haven’t seen this happen before - are you still experiencing this issue? If so, we are available on chat or via email so we can look into this further!


#6

Thanks, @lindsey. Having a nice chat with @nbeyer even as I write this. Hopefully I can clarify the bug (with reproduction steps) there and she can pass it along to whomever.


Rolling average using offset_list in table calculations (3.36+)
(Esteban Paredez) #7

is it possible to delete the first “percent change” ?
i dont want to show those “0”.

thank you in advance.


(diego.campos) #8

Hi @Esteban_Paredez,

Yep, you can do that by filtering for instance with a Yes / No calculation and then choose the "Hide “No"s From Visualization” option in the cog to hide those rows in any visualization type.

Here you have more info on how to do it: Filtering in Visualizations using Table Calculations (3.28+).

In this example, it would something like:

NOT(is_null(${percent_change_calculation}) OR ${percent_change_calculation} = 0)

Thanks!


(Izzy) #9

Or, if you don’t want to create another table calculation, you could go to the Chart Settings panel and select “Hide the first 1 Rows” at the bottom! Either’d work.


(Ben) #10

Hey Izzy, I actually think Esteban was looking to remove the first column that shows nulls (in this case the 10 to 19 one) because there is no column before it to show the % difference against.

I am also looking to do this, I think I need some form of pivot_index but not entirely sure how. Any ideas?

Thanks


(Izzy) #11

Oh, my bad, you’re totally right @Benjamin, I missed the mark there.

@Esteban_Paredez, I don’t think it’s possible to hide a specific field in a pivot column. You could theoretically use pivot_where() to create the calculations manually, by saying
pivot_where(${age_tier}="20 to 29",${percent change}) and adding a new calculation for each age tier you want to show a column for, excluding the first one.

The issue is that it wouldn’t be dynamic (ie: wouldn’t adjust to new pivot values appearing, since you’ve hardcoded the “20 to 29”) and you’d have to rebuild it manually every time.

A possible quicker “solution” in the meantime could be to change the calculation to say something more useful than ø, so it’s not as confusing.

if(
is_null(
  
(${orders.count} / pivot_offset(${orders.count}, -1)) - 1)
,"No % change, base value",
to_string((${orders.count} / pivot_offset(${orders.count}, -1)) - 1)
)

thanks for clarifying!


(Ben) #12

Hey Izzy,

Makes a lot of sense, thanks for the explanation, as you say I would need it to be dynamic, I was thinking that like this formula below would only show the last column there may be a way to inverse this to only show all but the first column, or maybe you just need a separate one of these for each column you do want;

pivot_index((${total_spend} / pivot_offset(${total_spend}, -1)) - 1,2)

But failing that I may use the “if” replacement

Thanks!

Ben