Visualizing Compound Growth Rate in a table/graph etc. (without adding calculations)

visualizations

#1

Hello,

I am designing a dynamic reporting where I compare several geographic locations and how they evolve through time (e.g. every month for the last 6 months)

I would like to create a column with the CAGR (compound growth rate) in the end to understand the trend easily and compare it with the target (easy via conditional formatting) and identify areas to prioritize

Is there a way to do that numerically and not by the trend visualization tool? I am putting all in a data table because with lots of data it becomes hard to understand with a graph

Ideally the CAGR function would recognize directly the number of weeks/months/years (N) and calculate automatically the growth rate and display a number

This need is recurrent as I always try to understand the trend in many different situations and with often a heavy dataset. I am sure plenty of people have similar needs

CAGRFormula
*do not need to be in years, N as time periods (day/week/month/year/semester etc.) would be great so it can be adapted to every situation


(romain.ducarrouge) #2

Hey @racsoaloirra, that is an interesting use case!

If I get the formula above right, you wish to do this:

If you wish to perform this directly in the SQL and not a table calculation, I believe you will need to use an intermediate Derived Table there.
The derived table will essentially build the output you have on the explore there, creating n columns (for n periods selected) and x rows for your regions.
The in the definition of the fields for the derived table you can reference the various created columns to perform the math you want to have.
The implementation of this will be specific to the dialect your database uses (functions…).

If you want to use table calcs; we can achieve this in the following way breaking down each step into a table calc (up to you later on to combine them all if you prefer):

  1. Finding the lastPivotValue:
    pivot_index(${ measure_field }, max(pivot_row(pivot_column())))

  2. Finding the firstPivotValue:
    pivot_index(${ measure_field }, 1)

  3. Finding the countOfPivots:
    max(pivot_row(pivot_column()))

  4. Calculating the equationOutput:
    power((${lastpivotvalue}/${firstpivotvalue}), (1/${countofpivots}))-1

The link below is an implementation of the logic defined here on the i__looker model.
BASE URL + /explore/i__looker/history?fields=look.id,query.count,history.most_recent_run_at_week&pivots=history.most_recent_run_at_week&fill_fields=history.most_recent_run_at_week&f[history.most_recent_run_at_date]=6+weeks&sorts=query.count+desc+0,history.most_recent_run_at_week&limit=500&query_timezone=Etc%2FGMT%2B2&vis=%7B%7D&filter_config=%7B%22history.most_recent_run_at_date%22%3A%5B%7B%22type%22%3A%22past%22%2C%22values%22%3A%5B%7B%22constant%22%3A%226%22%2C%22unit%22%3A%22wk%22%7D%2C%7B%7D%5D%2C%22id%22%3A0%2C%22error%22%3Afalse%7D%5D%7D&dynamic_fields=%5B%7B%22table_calculation%22%3A%22firstpivotvalue%22%2C%22label%22%3A%22firstPivotValue%22%2C%22expression%22%3A%22pivot_index%28%24%7Bquery.count%7D%2C+1%29%22%2C%22value_format%22%3Anull%2C%22value_format_name%22%3Anull%2C%22is_disabled%22%3Afalse%2C%22_kind_hint%22%3A%22supermeasure%22%2C%22_type_hint%22%3A%22number%22%7D%2C%7B%22table_calculation%22%3A%22lastpivotvalue%22%2C%22label%22%3A%22lastPivotValue%22%2C%22expression%22%3A%22pivot_index%28%24%7Bquery.count%7D%2C+max%28pivot_row%28pivot_column%28%29%29%29%29%22%2C%22value_format%22%3Anull%2C%22value_format_name%22%3Anull%2C%22_kind_hint%22%3A%22supermeasure%22%2C%22_type_hint%22%3A%22number%22%7D%2C%7B%22table_calculation%22%3A%22countofpivots%22%2C%22label%22%3A%22countOfPivots%22%2C%22expression%22%3A%22max%28pivot_row%28pivot_column%28%29%29%29%22%2C%22value_format%22%3Anull%2C%22value_format_name%22%3Anull%2C%22_kind_hint%22%3A%22supermeasure%22%2C%22_type_hint%22%3A%22number%22%7D%2C%7B%22table_calculation%22%3A%22equationoutput%22%2C%22label%22%3A%22equationOutput%22%2C%22expression%22%3A%22power%28%28%24%7Blastpivotvalue%7D%2F%24%7Bfirstpivotvalue%7D%29%2C+%281%2F%24%7Bcountofpivots%7D%29%29-1%22%2C%22value_format%22%3Anull%2C%22value_format_name%22%3Anull%2C%22_kind_hint%22%3A%22supermeasure%22%2C%22_type_hint%22%3A%22number%22%7D%5D&origin=share-expanded

Please let us know if this helps.
Also feel free to email us at support@looker.com with further details on your use case if this is not answering your question.
Cheers,
-Romain