# Re: A Solution for Dividing/Summing/Multiplying by the Previous Value!

533 0
cancel
Showing results for
Did you mean:
7 - App Architect

Hey Y’all,

So I’m working on a use case where I need to determine metrics for a funnel. I want to know two things:

1. the percent of value that remains in the funnel from the previous step, and
2. the percent remaining from the beginning of the funnel.

So that looks like:

Metric A - 10,000 | 100% | 100%
Metric B - 5,000 | 50% | 50%
Metric C - 1,000 | 20% | 10%
Metric D - 500 | 50% | 5%

This of course would require me to use the previous value in a function, something that Airtable isn’t built for on its own, but it is something that people have found great workarounds for, like @W_Vann_Hall who has covered a great workaround thoroughly in this post. I happen to be dividing, but this is also great if you’re looking to do a running balance as well.

To make this easier and cleaner, and just to see if I could do it (tbh), I tried my hand at a script that will do this in a snap.

The Pros: No complicated rollups
The Cons: Since it uses the scripting block, it doesn’t update automatically.

Here’s a a video of me walking through the base setup and the script, and here’s the example base, which has a couple of other scripts in it was well for you to play around with. I’d love to know how you’d improve this or use this, and happy Airtabling and Scripting!

:raising_hand_woman:t5: :raising_hand_woman:t5: :raising_hand_woman:t5: I want to mention that before using the scripting block I NEVER used JavaScript. I had a bit of VBA background and made some messy Macros in my Excel days. If I can I can do this, anyone learning this can too. It just takes a lot of googling, paticence, and taking a walk when you run into problems.

Script below. I’m using @Jeremy_Oglesby’s great batchAnd() function so that I can update more than 50 records at a time.

``````let funnelTable = base.getTable('Funnel Metrics');
let funnelQuery = await funnelTable.selectRecordsAsync();
let funnelRecords = funnelQuery.records;
let months = funnelTable.getField('Month').options.choices;
let years = funnelTable.getField('Year').options.choices;
let metrics = funnelTable.getField('Metric').options.choices;

// Goal: use the preceding number in the funnel to calcualte the percentage
// For each group - create an array of the funnel numbers

// Create an array of the values you need, as well as the ID of the record so that you can update it later.
// I've also added a filter to this array, because there are some records in this table that I don't want to include in this calculation
let recordsMap = funnelRecords.map(c => ({

'include': c.getCellValue('Include in Funnel Calculation'),
'value': c.getCellValue('Value'),
'id': c.id

})).filter( x => x.include == 'Yes' );

// This is where you'll definitely need to customize for yourself.
// Each of my campaigns has 4 steps, so I can decided to splice my recordsMap array every 4 values.
// Depending on how your information is structured you'll need to figure out the best way to separate your records into the groups you want to divide/add/subtract/mulitply by
let splicedArray = []
while (recordsMap.length > 0) splicedArray.push(recordsMap.splice(0, 4));

// Using forEach and reduce, I'm able to go through each of the splicedArrays in splicedArray
// and use the reduce function to caluclate the percentage and add two new key value pairs to the objects in each splicedArray
splicedArray.forEach(function(array){

array.reduce(function(acc, curr, i, array){

curr.funnelVal = curr.value / array[i - 1].value;
curr.totalFunnelVal = curr.value / array[0].value

})

});

// Getting this in the right format for batchAnd as well as updateRecordsAsync
let update = splicedArray.flat().filter(x => x.funnelVal != undefined).map( c => ({id: c.id, fields:{

'Funnel Retention by Step': c.funnelVal,
'Funnel Retention - Overall': c.totalFunnelVal

}}))

/*
Use this function to perform 'Update', 'Create', or 'Delete'
async actions on batches of records that could potentially
more than 50 records.

::PARAMETERS::
action = string; one of 3 values:
- 'Update' to call table.updateRecordsAsync()
- 'Create' to call table.createRecordsAsync()
- 'Delete' to call table.deleteRecordsAsync()

table = Table; the table the action will be performed in

records = Array; the records to perform the action on
- Ensure the record objects inside the array are
formatted properly for the action you wish to
perform

::RETURNS::
recordsActedOn = integer, array of recordId's, or null;
- Update Success: integer; the number of records processed by the function
- Delete Success: integer; the number of records processed by the function
- Create Success: array; the id strings of records created by the function
- Failure: null;
*/
async function batchAnd(action, table, records) {
let recordsActedOn;

switch (action) {
case 'Update':
recordsActedOn = records.length;
while (records.length > 0) {
await table.updateRecordsAsync(records.slice(0, 50));
records = records.slice(50);
};
break;

case 'Create':
recordsActedOn = [];
while (records.length > 0) {
let recordIds = await table.createRecordsAsync(records.slice(0, 50));
recordsActedOn.push(...recordIds)
records = records.slice(50);
};
break;

case 'Delete':
recordsActedOn = records.length;
while (records.length > 0) {
await table.deleteRecordsAsync(records.slice(0, 50));
records = records.slice(50);
}
break;

default:
output.markdown(`**Please use either 'Update', 'Create', or 'Delete' as the "action" parameter for the "batchAnd()" function.**`);
recordsActedOn = null;
}
return recordsActedOn;
};

await batchAnd('Update',funnelTable,update);``````
4 - Data Explorer

@VictoriaPlummer I think your solution here is on track for what I’m trying to do but I’m having trouble making the leap.

I have external users inputting new financial information regularly. Each time, they create a ratio, that, when divided, becomes something we call their "share price. So if the ratio is \$100/\$2, then it’s a \$50 share price (100/2). What I’m trying to do is create a few different ways to compare those entries and the “share price” it creates.

For example, if you inputted your information on 12/31/2017 and the share price was \$50
Then you inputted your info on 12/31/2018 and the share price was \$75
I want to calculate that the change was +50% (\$75 is 50% bigger than \$50).
When you input your info on 12/31/2019 and the share price was \$100.
Now I want to show that your 1-year change from 2018 to 2019 is 50% and your 2-year change was 100%

And to be able to calculate that for each entry that gets put in.

Your funnel calculator is close but I don’t understand it enough to figure it out for my needs.

Help!