Discussions

Expand all | Collapse all

Need help with formula for tiered pricing

  • 1.  Need help with formula for tiered pricing

    Posted 20 days ago
    Hello,

    I'm trying to build a formula that will calculate the appropriate pricing based on a tier. So for example, I have the following tiered matrix:

    <=250,000 transactions      *  $0.05
    <=500,000 transactions      *  $0.03
    <=1,000,000 transactions   *  $0.01

    So I created the following:

    If(
    [Transactions]<=250000,[Transactions]*0.05,
    [Transactions]>250000 and [Transactions]<=500000, [Transactions]*0.03,
    [Transactions]>1000000, [Transactions]*0.01)

    I feel like this is wrong. Like if the # of transactions is greater than 250000, then it's going to price all the transactions at the 250000 rate, as opposed to just the transactions over the tier limit... How can I fix that?

    Thanks!




    ------------------------------
    Christine
    ------------------------------


  • 2.  RE: Need help with formula for tiered pricing

    Posted 20 days ago
    Edited by Austin K 20 days ago
    Quick Base takes the first thing that is true from the formula and runs with it so you need to do it in a certain way as you suspected.

    If(
    [Transactions]<=250000,[Transactions]*0.05,
    [Transactions]<=500000, [Transactions]*0.03,
    [Transactions]>500000, [Transactions]*0.01)

    So it hits the first line if the Transactions are less than or equal to 250,000. If the amount is higher then it goes to the second line which now means the amount is definitely above 250,001 so the formula checks to see if the amount is less than or equal to 500,000. If it is then it stops there and spits out [Transactions]*0.03, if not it continues to the next line as it knows the number is 500,001 or more.

    In your formula I capped it to be more than 500k is 0.01 but you had it set to be more than 1 million, so it went from 500k or less to 1 million or more. Did you want another tier in between them?


  • 3.  RE: Need help with formula for tiered pricing

    Posted 20 days ago
    Thanks for your response!

    That was just a typo on my part. What I would like for it to do is when it validates the arguments and runs the calculation for that tier, I also need it to incorporate the pricing for the previous. So, instead of taking all the transactions and multiplying them by the amount for the tier... I need it to calculate the first 250000 at the first pricing, the second level at that pricing and so on.

    The way I'm reading it, if the # of transactions are higher than the first tier, it will multiply all the transactions by the amount listed in the tier they fall into which isn't what I'm looking for but I'm not sure how to fix that.​

    ------------------------------
    ChristinePratts
    ------------------------------



  • 4.  RE: Need help with formula for tiered pricing

    Posted 20 days ago
    I had wrote something out but it seems like you may have something wrong in the formula. Is [Transactions] just the number of transactions?

    What you would want to have there is the "Spend/Cost/Amount" field for the specific record you are working on. Not the number of transactions but the actual dollar amount for that specific transaction.

    Are these single large purchases or is this like a client that has a subscription with you(or someone else) where they are spending that amount over the whole year?


  • 5.  RE: Need help with formula for tiered pricing

    Posted 20 days ago
    Try this

    Min([250000, [Transactions]) * 0.05  // 5 cents times the smaller of 250000 and the # of Transactions.
    +  
    Max(500000, Max(0,[Transactions]-250000))* 0.03 // the inner max keeps the result above zero, the first max limits to 500000
    +
    Max(0, [Transactions]-500000) * 0.01 // the max keeps the result above zero

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 6.  RE: Need help with formula for tiered pricing

    Posted 20 days ago
    Thanks. I'll give that a try!

    ------------------------------
    ChristinePratts
    ------------------------------