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

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);
3 Likes

@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!