Skip to main content

Percent of previous value

The percent of previous value gives you the percent of a value compared to the value in the row above it.

Just gimme the code! 👾

Here's an example of a percent of previous value 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})

In general, the SQL used for calculating the percent of 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 of the previous value​

${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}
  )
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})
  , 2)