How to Build a Compound Annual Growth Rate Model in CRM Analytics
As a Solution Engineer for CRM Analytics for 5 years, I got my start working with the product as a technology consultant in 2015 when the product was called Wave Analytics, where I was building complex bookings and forecasting dashboards for Salesforce’s corporate finance team diving deep into SAQL and bindings. Since then the product has matured and so has the support that is available to its developer and user community with resources like Salesforceblogger. I’ve used Salesforceblogger to help me with my customer engagements in solving all sorts of challenges related to CRM Analytics, so I’m honored to have the opportunity to contribute to this blog for the first time with a unique example of how to use SAQL and bindings. I got the inspiration for writing this blog when I was working with a customer this year in need of support for their forecasting use case which required a custom Compound Annual Growth Rate (CAGR) calculation. While I was able to help my customer with their specific use case, I hope this blog which includes a build-along example where a dataset can serve as another useful asset for the CRM Analytics community and all of its trailblazers.
Note: Parts of this solution can be substituted with input widgets once that is available in Winter 23. However, the approach still has many other use cases.
So what can CAGR models be used for within CRM Analytics? CAGR models can be used for creating retirement calculators for financial advisors working with their clients who have investment portfolios. It can include additional factors like their annual contribution amount or distribution amount which requires you to use more than the out-of-the-box power function. CAGR models can also be used to forecast the revenue of a SAAS company or a specific product line at a company, like Salesforce, and include additional factors in the calculation such as attrition rates.
In this blog, we will walk through the first example of building a retirement calculator within CRM Analytics where a client can review their investment portfolio’s current value and forecast the value of their account into a future year based on a selected growth rate and annual contribution amount. In the dashboard example, we will have a ‘Current Investment Value’ which will query from a dataset called ‘retirement_account_balance’. The ‘Growth Rate’ and ‘Annual Contributions’ selectors will query from a dataset called ‘parameters’ to define the available values, and the ‘Forecast Model Years’ selector will use a static step to define the years we are modeling for. Lastly, we will have one custom SAQL query which contains all of our ‘CAGR logic’ and will be used to plot the forecasted model.
Before jumping into details, we will be using a few concepts related to bindings and SAQL so if you’re new to the platform, this is a good time to start learning more and familiarize yourself with those topics.
Step 1: Upload Datasets for ‘retirement account balance’ and ‘parameters’
In Analytics Studio > Create > Upload the following two datasets:
Retirement account balance: This dataset includes hypothetical investment portfolio data where an individual owns 1000 shares of the S&P500 index fund $SPY. There are rows of data to show the starting month value from May 1, 2021 to April 1, 2022.
Parameters: This dataset includes 3 fields. Type, Parameter, and Parameter Display. Prior to the Winter23 release of Salesforce, the concept of parameters is not a standard feature and so we will use a workaround by creating a dataset to identify 3 parameters that we can use as selectors in our dashboard.
In parameters, change the Paramater_Display field to a Dimension when uploading the file. Having a unique dimension value will allow you to create a list of values in which you can later select your ‘growth rate’ and ‘annual contribution’ inputs.
Step 2: Query Dataset for Investment Current Value
Step 2 will return the portfolio balance by month over time from May 2021 to April 2022 in a chart widget. In this example, the Beginning Monthly Balance for April 2022 returned in this chart will serve as the start value for the CAGR calculation.
In a new blank dashboard, Create Query and select the dataset called retirement account balance. In a lens, group by Date (Year-Month) on the Time Axis and sum Total Value on the Y-Axis as a Timeline chart type. Click update and drag the step onto the canvas, and add a title. This gives us the Portfolio Beginning Month Balance over Time.
Add the widget to the canvas and save the dashboard. The chart of the Retirement Portfolio Beginning Month Balance is actually a hypothetical scenario where you held 1000 shares of the $SPY index fund and its associated price on the first day of the month from May 2021 to April 2022.
Step 3: Add Growth Rate and Annual Contributions Selectors via Parameters Dataset
In this step, we are first going to use the parameters dataset to create a selector for growth rate by filtering by Type = Parameter 2. This will filter the parameter dataset to only return values from 1-100. Filtering by Parameter 1 returns values of 1-10 and filtering by Parameter 3 returns values of 100 to 100,000 in increments of 100.
Note: The ‘parameters’ dataset is just a sample and the dataset values should reflect the selections you expect in your dashboard.
Filter Lens by Type = Parameter 2.
Group by Parameter_Display under Bars and sum Parameter under Bar Length then sort sum of Parameter in Ascending order. Click Update when complete.
Once you have created the lens growth rate, add a list widget onto the canvas and snap the growth rate query to the list widget.
Under the WIDGET tab change the title to Annual Growth Rate.
Under the QUERY tab, change Display Label to Growth Rate %, unselect Apply Global Filters, and unselect Broadcast selections as facets. This is very important that we unselect the faceting and filter of the parameters dataset because we are using the parameters dataset multiple times for different inputs in our dashboard. Leaving global filters or broadcast facets on would not allow for Growth Rate and Annual Contribution parameters to display all values.
Also, set your Selection Type to Single selection (required).
Next, repeat these steps to create a lens and set widget and query properties for Annual Contribution. In Annual Contribution, instead, we will filter by Type = Parameter 3 which will return values from 100 to 100,000 in increments of $100.
The result of this step is you should have a chart for your Portfolio over time and also selectors for Annual Growth Rate and Annual Contribution. Make sure you save what you’ve created so far!
Step 4: Create Static Step for Forecast Model Years
In this step, we will create a static step that returns two fields. The first field is the year to which we want to forecast the model and the second field, “query end”, returns a line of SAQL that is a union statement that appends all the years calculated in the model from the current year to the forecasted year. In my static step, I have created roughly 20 years forecasting out to 2043 but you can create fewer or more years depending on your project requirements.
The normal editor is a bit difficult to edit the text for “query end” so one trick is after you create the 2 fields and a few rows, snap the query to a widget on the dashboard as a list widget or a pillbox widget and open the query from the advanced editor. This allows you to update the query in a free-form text editor that only focuses on the static step portion of the JSON.
I’ve included the code block below for the values related to the forecasting year 2023 through 2043.
"values": [
{
"Display": "2023",
"query end": "q = union q2022, q2023;"
},
{
"Display": "2024",
"query end": "q = union q2022, q2023, q2024;"
},
{
"Display": "2025",
"query end": "q = union q2022, q2023, q2024, q2025;"
},
{
"Display": "2026",
"query end": "q = union q2022, q2023, q2024, q2025, q2026;"
},
{
"Display": "2027",
"query end": "q = union q2022, q2023, q2024, q2025, q2026, q2027;"
},
{
"Display": "2028",
"query end": "q = union q2022, q2023, q2024, q2025, q2026, q2027, q2028;"
},
{
"Display": "2029",
"query end": "q = union q2022, q2023, q2024, q2025, q2026, q2027, q2028, q2029;"
},
{
"Display": "2030",
"query end": "q = union q2022, q2023, q2024, q2025, q2026, q2027, q2028, q2029, q2030;"
},
{
"Display": "2031",
"query end": "q = union q2022, q2023, q2024, q2025, q2026, q2027, q2028, q2029, q2030, q2031;"
},
{
"Display": "2032",
"query end": "q = union q2022, q2023, q2024, q2025, q2026, q2027, q2028, q2029, q2030, q2031, q2032;"
},
{
"Display": "2033",
"query end": "q = union q2022, q2023, q2024, q2025, q2026, q2027, q2028, q2029, q2030, q2031, q2032, q2033;"
},
{
"Display": "2034",
"query end": "q = union q2022, q2023, q2024, q2025, q2026, q2027, q2028, q2029, q2030, q2031, q2032, q2033, q2034;"
},
{
"Display": "2035",
"query end": "q = union q2022, q2023, q2024, q2025, q2026, q2027, q2028, q2029, q2030, q2031, q2032, q2033, q2034, q2035;"
},
{
"Display": "2036",
"query end": "q = union q2022, q2023, q2024, q2025, q2026, q2027, q2028, q2029, q2030, q2031, q2032, q2033, q2034, q2035, q2036;"
},
{
"Display": "2037",
"query end": "q = union q2022, q2023, q2024, q2025, q2026, q2027, q2028, q2029, q2030, q2031, q2032, q2033, q2034, q2035, q2036, q2037;"
},
{
"Display": "2038",
"query end": "q = union q2022, q2023, q2024, q2025, q2026, q2027, q2028, q2029, q2030, q2031, q2032, q2033, q2034, q2035, q2036, q2037, q2038;"
},
{
"Display": "2039",
"query end": "q = union q2022, q2023, q2024, q2025, q2026, q2027, q2028, q2029, q2030, q2031, q2032, q2033, q2034, q2035, q2036, q2037, q2038, q2039;"
},
{
"Display": "2040",
"query end": "q = union q2022, q2023, q2024, q2025, q2026, q2027, q2028, q2029, q2030, q2031, q2032, q2033, q2034, q2035, q2036, q2037, q2038, q2039, q2040;"
},
{
"Display": "2041",
"query end": "q = union q2022, q2023, q2024, q2025, q2026, q2027, q2028, q2029, q2030, q2031, q2032, q2033, q2034, q2035, q2036, q2037, q2038, q2039, q2040, q2041;"
},
{
"Display": "2042",
"query end": "q = union q2022, q2023, q2024, q2025, q2026, q2027, q2028, q2029, q2030, q2031, q2032, q2033, q2034, q2035, q2036, q2037, q2038, q2039, q2040, q2041, q2042;"
},
{
"Display": "2043",
"query end": "q = union q2022, q2023, q2024, q2025, q2026, q2027, q2028, q2029, q2030, q2031, q2032, q2033, q2034, q2035, q2036, q2037, q2038, q2039, q2040, q2041, q2042, q2043;"
}
],Now you have created all your input selectors. Growth Rate and Annual Contribution are list widgets and Forecast Model is a Pillbox. For the Forecast Model widget under Query Properties, select Single Required as the Selection Type. As another reminder, save early and save often!
Step 5: Create Logic for CAGR Model
The next step is to build out the logic for the CAGR forecast model. There are a few different things to consider when building out this query in SAQL.
- In SAQL, you need to load a dataset in order to write a calculation even if you don’t use any value from that dataset.
- SAQL does not allow us to iterate through a calculation using a “loop” so we will need to manually script each year in our plot.
- We can use bindings within the SAQL query to return the Current Investment Value of our forecast model as the first value. We will pull in the last value (in this case for April 2022) from the query in step 2.
- We can use bindings for the following variables in the calculation:
- Growth Rate
- Annual Contribution
- We can use bindings within the SAQL query to return the union statement that selects the years to plot in our dynamic forecast model.
Before creating the query in the dashboard, we are going to create a query pseudo-code in a text editor that we can replace variables with the appropriate bindings, text updates, and text wrappings before pasting the code into the advanced editor in step 6 further down.
Here is what the Pseudo code looks like for our CAGR Forecast Model. In each line of the script from q2023 through q2043 we are able to call the previous year in the foreach statement to return the ‘Forecast’ value of the previous year and multiply by the growth rate and add the annual contribution.
--load a dataset as required by SAQL and group by all to return just a single row of data.
q = load "retirement_account_balance";
q = group q by all;
-- return the current investment total value for 2022
q2022 = foreach q generate "2022" as 'Year', 1*[Current Investment Value] as 'Forecast';
-- create a script for all the years in your model and static step. Hard code the 'Year' value and bind the [Annual Growth Rate] and [Annual Contribution]
q2023 = foreach q2022 generate "2023" as 'Year', 'Forecast' * (1+.01*[Annual Growth Rate]) + [Annual Contribution] as 'Forecast';
q2024 = foreach q2023 generate "2024" as 'Year', 'Forecast' * (1+.01*[Annual Growth Rate]) + [Annual Contribution] as 'Forecast';
q2025 = foreach q2024 generate "2025" as 'Year', 'Forecast' * (1+.01*[Annual Growth Rate]) + [Annual Contribution] as 'Forecast';
q2026 = foreach q2025 generate "2026" as 'Year', 'Forecast' * (1+.01*[Annual Growth Rate]) + [Annual Contribution] as 'Forecast';
q2027 = foreach q2026 generate "2027" as 'Year', 'Forecast' * (1+.01*[Annual Growth Rate]) + [Annual Contribution] as 'Forecast';
q2028 = foreach q2027 generate "2028" as 'Year', 'Forecast' * (1+.01*[Annual Growth Rate]) + [Annual Contribution] as 'Forecast';
q2029 = foreach q2028 generate "2029" as 'Year', 'Forecast' * (1+.01*[Annual Growth Rate]) + [Annual Contribution] as 'Forecast';
q2030 = foreach q2029 generate "2030" as 'Year', 'Forecast' * (1+.01*[Annual Growth Rate]) + [Annual Contribution] as 'Forecast';
q2031 = foreach q2030 generate "2031" as 'Year', 'Forecast' * (1+.01*[Annual Growth Rate]) + [Annual Contribution] as 'Forecast';
q2032 = foreach q2031 generate "2032" as 'Year', 'Forecast' * (1+.01*[Annual Growth Rate]) + [Annual Contribution] as 'Forecast';
q2033 = foreach q2032 generate "2033" as 'Year', 'Forecast' * (1+.01*[Annual Growth Rate]) + [Annual Contribution] as 'Forecast';
q2034 = foreach q2033 generate "2034" as 'Year', 'Forecast' * (1+.01*[Annual Growth Rate]) + [Annual Contribution] as 'Forecast';
q2035 = foreach q2034 generate "2035" as 'Year', 'Forecast' * (1+.01*[Annual Growth Rate]) + [Annual Contribution] as 'Forecast';
q2036 = foreach q2035 generate "2036" as 'Year', 'Forecast' * (1+.01*[Annual Growth Rate]) + [Annual Contribution] as 'Forecast';
q2037 = foreach q2036 generate "2037" as 'Year', 'Forecast' * (1+.01*[Annual Growth Rate]) + [Annual Contribution] as 'Forecast';
q2038 = foreach q2037 generate "2038" as 'Year', 'Forecast' * (1+.01*[Annual Growth Rate]) + [Annual Contribution] as 'Forecast';
q2039 = foreach q2038 generate "2039" as 'Year', 'Forecast' * (1+.01*[Annual Growth Rate]) + [Annual Contribution] as 'Forecast';
q2040 = foreach q2039 generate "2040" as 'Year', 'Forecast' * (1+.01*[Annual Growth Rate]) + [Annual Contribution] as 'Forecast';
q2041 = foreach q2040 generate "2041" as 'Year', 'Forecast' * (1+.01*[Annual Growth Rate]) + [Annual Contribution] as 'Forecast';
q2042 = foreach q2041 generate "2042" as 'Year', 'Forecast' * (1+.01*[Annual Growth Rate]) + [Annual Contribution] as 'Forecast';
q2043 = foreach q2042 generate "2043" as 'Year', 'Forecast' * (1+.01*[Annual Growth Rate]) + [Annual Contribution] as 'Forecast';
-- plot years in forecast model
[Years in Forecast Model]We are going to replace the following variables and functions with bindings below. Your bindings may look slightly different if you have different step or column names.
-- binding for [Current Investment Value]
{{valueAt(column(Date_Year_Date_Month_1.result, ["sum_Total_Value"]),-1).asString()}}
-- binding for [Growth Rate]
{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}
-- binding for [Annual Contribution]
{{cell(Parameter_Display_2.selection, 0, "sum_Parameter").asString()}}
-- binding for returning plot of [Years in Forecast Model]
{{cell(static_1.selection, 0, "query end").asString()}}
You can always use the advanced editor to help build the bindings for [Current Investment Value], [Growth Rate], [Annual Contribution], and [Years in Forecast Model] respectively.
Once you have mapped out your bindings for each of the variables using the advanced editor, replace the pseudo code with the bindings in your local text editor.
q = load "retirement_account_balance";
q = group q by all;
q2022 = foreach q generate "2022" as 'Year', 1*{{valueAt(column(Date_Year_Date_Month_1.result, ["sum_Total_Value"]),-1).asString()}} as 'Forecast';
q2023 = foreach q2022 generate "2023" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, "sum_Parameter").asString()}} as 'Forecast';
q2024 = foreach q2023 generate "2024" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, "sum_Parameter").asString()}} as 'Forecast';
q2025 = foreach q2024 generate "2025" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, "sum_Parameter").asString()}} as 'Forecast';
q2026 = foreach q2025 generate "2026" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, "sum_Parameter").asString()}} as 'Forecast';
q2027 = foreach q2026 generate "2027" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, "sum_Parameter").asString()}} as 'Forecast';
q2028 = foreach q2027 generate "2028" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, "sum_Parameter").asString()}} as 'Forecast';
q2029 = foreach q2028 generate "2029" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, "sum_Parameter").asString()}} as 'Forecast';
q2030 = foreach q2029 generate "2030" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, "sum_Parameter").asString()}} as 'Forecast';
q2031 = foreach q2030 generate "2031" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, "sum_Parameter").asString()}} as 'Forecast';
q2032 = foreach q2031 generate "2032" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, "sum_Parameter").asString()}} as 'Forecast';
q2033 = foreach q2032 generate "2033" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, "sum_Parameter").asString()}} as 'Forecast';
q2034 = foreach q2033 generate "2034" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, "sum_Parameter").asString()}} as 'Forecast';
q2035 = foreach q2034 generate "2035" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, "sum_Parameter").asString()}} as 'Forecast';
q2036 = foreach q2035 generate "2036" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, "sum_Parameter").asString()}} as 'Forecast';
q2037 = foreach q2036 generate "2037" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, "sum_Parameter").asString()}} as 'Forecast';
q2038 = foreach q2037 generate "2038" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, "sum_Parameter").asString()}} as 'Forecast';
q2039 = foreach q2038 generate "2039" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, "sum_Parameter").asString()}} as 'Forecast';
q2040 = foreach q2039 generate "2040" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, "sum_Parameter").asString()}} as 'Forecast';
q2041 = foreach q2040 generate "2041" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, "sum_Parameter").asString()}} as 'Forecast';
q2042 = foreach q2041 generate "2042" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, "sum_Parameter").asString()}} as 'Forecast';
q2043 = foreach q2042 generate "2043" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, "sum_Parameter").asString()}} as 'Forecast';
{{cell(static_1.selection, 0, "query end").asString()}}In your text editor such as sublime turn regular expressions ON. This is where you need to format the SAQL to prepare it for embedding into JSON. We can do this with the following steps using find and replace the following values in find and replace with RegEx ON will replace “ with \”
The following values in find and replace with RegEx ON will wrap the dashboard into a single statement that can easily be copied and pasted into the query section of the JSON using the advanced editor.
Here is the output of the final query to embed into your step:
q = load "retirement_account_balance";\nq = group q by all;\nq2022 = foreach q generate "2022" as 'Year', 1*{{valueAt(column(Date_Year_Date_Month_1.result, ["sum_Total_Value"]),-1).asString()}} as 'Forecast';\nq2023 = foreach q2022 generate "2023" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, \"sum_Parameter\").asString()}} as 'Forecast';\nq2024 = foreach q2023 generate "2024" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, \"sum_Parameter\").asString()}} as 'Forecast';\nq2025 = foreach q2024 generate "2025" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, \"sum_Parameter\").asString()}} as 'Forecast';\nq2026 = foreach q2025 generate "2026" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, \"sum_Parameter\").asString()}} as 'Forecast';\nq2027 = foreach q2026 generate "2027" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, \"sum_Parameter\").asString()}} as 'Forecast';\nq2028 = foreach q2027 generate "2028" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, \"sum_Parameter\").asString()}} as 'Forecast';\nq2029 = foreach q2028 generate "2029" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, \"sum_Parameter\").asString()}} as 'Forecast';\nq2030 = foreach q2029 generate "2030" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, \"sum_Parameter\").asString()}} as 'Forecast';\nq2031 = foreach q2030 generate "2031" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, \"sum_Parameter\").asString()}} as 'Forecast';\nq2032 = foreach q2031 generate "2032" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, \"sum_Parameter\").asString()}} as 'Forecast';\nq2033 = foreach q2032 generate "2033" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, \"sum_Parameter\").asString()}} as 'Forecast';\nq2034 = foreach q2033 generate "2034" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, \"sum_Parameter\").asString()}} as 'Forecast';\nq2035 = foreach q2034 generate "2035" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, \"sum_Parameter\").asString()}} as 'Forecast';\nq2036 = foreach q2035 generate "2036" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, \"sum_Parameter\").asString()}} as 'Forecast';\nq2037 = foreach q2036 generate "2037" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, \"sum_Parameter\").asString()}} as 'Forecast';\nq2038 = foreach q2037 generate "2038" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, \"sum_Parameter\").asString()}} as 'Forecast';\nq2039 = foreach q2038 generate "2039" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, \"sum_Parameter\").asString()}} as 'Forecast';\nq2040 = foreach q2039 generate "2040" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, \"sum_Parameter\").asString()}} as 'Forecast';\nq2041 = foreach q2040 generate "2041" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, \"sum_Parameter\").asString()}} as 'Forecast';\nq2042 = foreach q2041 generate "2042" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, \"sum_Parameter\").asString()}} as 'Forecast';\nq2043 = foreach q2042 generate "2043" as 'Year', 'Forecast'*(1+.01*{{cell(Parameter_Display_1.selection, 0, "sum_Parameter").asString()}}) + {{cell(Parameter_Display_2.selection, 0, \"sum_Parameter\").asString()}} as 'Forecast';\n\n\n{{cell(static_1.selection, 0, "query end").asString()}}Step 6: Add CAGR Query Logic to a Step and Widget
Back on your dashboard, Create Query from retirement_account_balance. While modifying the lens, switch to Query mode and click Done to return the default query. This creates the JSON structure for the step which will allow you to embed your logic.
Add the query to the canvas as a chart widget and open the Advanced Editor. Under the Query tab, search for the “query” tag in the JSON. This is where you are going to replace the existing query with the CAGR query logic that you created in step 5.
Make sure you remove the columnMap tags from both the Widget and Query tabs of the JSON, your JSON is properly formatted, then Save. This will ensure that the widget displays correctly on your dashboard instead of throwing an error. If you are unfamiliar with the errors that occur in dashboards related to columnMap and bindings, you can read more about them here.
The hard work is now done. You can apply formatting to your dashboard and change the forecast model to a line chart instead of a bar chart or add in other design preferences like titles and colors. One final reminder to save your dashboard. Give it a test run by clicking on Preview and selecting a value for Growth Rate, Annual Contribution, and Forecast Year.
Voila! You’ve created a CAGR Retirement Calculator in CRM Analytics!
Written by
