Skip to main content

Accounting for unevenly distrubuted payments

  • October 30, 2017
  • 3 replies
  • 70 views
H
Forum|alt.badge.img+4

I want to write a field formula that helps our team know if we are on track for conference payments.

I have a field for our ticket goal, how many tickets have been sold, and the deadline date.

I want to see how many tickets we need to sell this week to meet our goal.

However!

With conference sales we get the bulk of our sales the last week. So, I want to write a formula that accounts for the closer we are to the deadline the more sales we should expect.

This is quite hard for me to Google. I’m hoping one of you smart, generous folks has an answer

    This topic has been closed for replies.

    3 replies

    P
    Forum|alt.badge.img+16
    • Polen
    • Known Participant
    • October 31, 2017

    I can totally see this happening using the function DATETIME_DIFF and then a relatively-complicated formula.

    A
    Forum|alt.badge.img+17
    • Andy_Lin1
    • Inspiring
    • November 8, 2017

    This is a very, very simplified calculation based on a cubic equation that has turning points (1,0) and (0,1), which gives a curve whose slope tapers at the maximum and minimum (you can use a higher-order equation for greater control over the shape of the curve or use a logarithmic function for a different type of curve altogether). I’ve chosen (1,0) and (0,1) to work with the decimal form of the percentages, but you can use y=.0002x^3-.03x^2+100 if you’re working with Airtable’s interger percentages, which range from 0 to 100.

    The key variable you need to do this sort of calculation is {Remaining Percentage of Days}, which is derived from ({Event Date} - TODAY) / ({Event Date} - {Ticket On Sale Date}). I’ve created a helper field with this number to improve the legibility of the formula, since we’re using this variable multiple times.

    The equation I’m using is y = 2x^3 - 3x^2 + 1
    https://www.google.ca/search?q=y%3D2x^3-3x^2%2B1

    Translated to an Airtable formula, it would look like this:
    {Total Tickets} * (2 * POWER({Remaining Percentage of Days},3) - 3 * POWER({Remaining Percentage of Days},2) + 1)

    Here’s a sample base of the formula in action:

    There’s an additional field at the end that applies the curve only for the last week, with the following formula. It’s similar to the above, but changes {Remaining Percentage of Days} to {Days Until Event}/7.
    IF({Days Until Event}>6,‘Minimal’,INT({Total Tickets}(2POWER({Days Until Event}/7,3)-3*POWER({Days Until Event}/7,2)+1)))

    You can of course combine curves using an IF formula, or start the final week curve at a higher base ticket count.

    Unfortunately, I don’t have a stats background so I can’t tell what type of function would work best for you (also, Airtable is missing some of the math functions available in Excel, so you may not necessarily be able to apply the functions you find).

      H
      Forum|alt.badge.img+4
      • Hashim_Warren
      • Author
      • Known Participant
      • November 10, 2017

      This is a very, very simplified calculation based on a cubic equation that has turning points (1,0) and (0,1), which gives a curve whose slope tapers at the maximum and minimum (you can use a higher-order equation for greater control over the shape of the curve or use a logarithmic function for a different type of curve altogether). I’ve chosen (1,0) and (0,1) to work with the decimal form of the percentages, but you can use y=.0002x^3-.03x^2+100 if you’re working with Airtable’s interger percentages, which range from 0 to 100.

      The key variable you need to do this sort of calculation is {Remaining Percentage of Days}, which is derived from ({Event Date} - TODAY) / ({Event Date} - {Ticket On Sale Date}). I’ve created a helper field with this number to improve the legibility of the formula, since we’re using this variable multiple times.

      The equation I’m using is y = 2x^3 - 3x^2 + 1
      https://www.google.ca/search?q=y%3D2x^3-3x^2%2B1

      Translated to an Airtable formula, it would look like this:
      {Total Tickets} * (2 * POWER({Remaining Percentage of Days},3) - 3 * POWER({Remaining Percentage of Days},2) + 1)

      Here’s a sample base of the formula in action:

      There’s an additional field at the end that applies the curve only for the last week, with the following formula. It’s similar to the above, but changes {Remaining Percentage of Days} to {Days Until Event}/7.
      IF({Days Until Event}>6,‘Minimal’,INT({Total Tickets}(2POWER({Days Until Event}/7,3)-3*POWER({Days Until Event}/7,2)+1)))

      You can of course combine curves using an IF formula, or start the final week curve at a higher base ticket count.

      Unfortunately, I don’t have a stats background so I can’t tell what type of function would work best for you (also, Airtable is missing some of the math functions available in Excel, so you may not necessarily be able to apply the functions you find).


      Very, very helpful! Thank you!

      Terms & ConditionsAccessibility statement