Skip to main content

Percent change from previous

The percent change gives you the percent change in a value compared to the value in the row above it. This calculation is very similar to percent of previous.

Just gimme the code! ๐Ÿ‘พ

Here's an example of a percent change calculation:

And here's the SQL that was used to generate it:

(
${pages.page_view_count} * 100 /
LAG(${pages.page_view_count}) OVER(ORDER BY ${pages.date_day}, ${pages.path})
) - 100

In general, the SQL used for calculating the percent change from the previous value has two bits (with an optional third bit):

  • column_I_want_to_compare - this is the column with the values you want to compare
  • column_I_want_to_order_by - this is the column you want to use to order the values you want to compare
  • optional_other_column_I_want_to_order_by - this column is optional and you can add as many more order by columns as you need. Normally, you'll need to add every dimension in your results table to the ORDER BY bit in your SQL. And, the order of these will need to be the same as the ordering you've added to the columns in your results table.

๐Ÿ‘€ Here's the SQL you can copy-paste to calculate the percent change from the previousโ€‹

(
${table.column_i_want_to_compare} * 100 /
LAG(${table.column_i_want_to_compare}) OVER(
ORDER BY
${table.column_I_want_to_order_by},
${table.optional_other_column_I_want_to_order_by}
)
) - 100
info

๐Ÿ’Ž Level up your SQL:

  • You can round your table calculation to only show a certain number of decimal points. Here's an example where I only show two decimal points:
ROUND(
(
${table.column_i_want_to_compare} * 100 /
LAG(${table.column_i_want_to_compare}) OVER(
ORDER BY
${table.column_I_want_to_order_by},
${table.optional_other_column_I_want_to_order_by}
)
) - 100
, 2)