Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

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

  • 1 October 2018
  • 1 reply
  • 931 views
R

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


53

1 reply

romain.ducarrouge
Rank
Userlevel 3
Badge

Hey @racsoaloirra, that is an interesting use case!


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


19


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 visit us at help.looker.com with further details on your use case if this is not answering your question.

Cheers,

-Romain

Reply


Terms of Service