IF formula for SKUs before and after exact date

Hello! This is our situation

We have a couple of boards that have IF statements based on 4 SKUs. On each board we input how many bottles of each SKU were ordered (sometimes manually, sometimes pulled w/integration), and so we get a subtotal column for each SKU + tax + shipping + etc.
However, prices changed last week and so I need a formula that will allow me to use X price before Oct 10 2022 and Y price after.

These are a couple of examples of the formulas already in place:

  1. This example is a column for one single SKU, but depending on the label it will mark different prices (for example employees have a secial price, if its a sample its 0). The one changing is the 331.03 priced one:

multiply({Limón},150),(IF({Canal#Labels} = “Empleado - Gratis”, multiply({Limón},0),(IF({Canal#Labels} = “Muestra Venta”, multiply({Limón},0),(IF({Canal#Labels} = “Muestra Mkt”, multiply({Limón},0),multiply({Limón},331.034483)))))))),2)

  1. But then I have this other type of formula when on 1 board we have multiple retailers, so the price for 1 SKU depends on the retailer (retailers edited for privacy):

IF({Retailer#Labels} = “1”, multiply({Junior},336.36),IF({Retailer#Labels} = “2”, multiply({Junior},325),(IF({Retailer#Labels} = “3”,multiply({Junior},334.55),(IF({Retailer#Labels} = “4”,multiply({Junior},311.32),(IF({Retailer#Labels} = “5”,multiply({Junior},325.26),(IF({Retailer#Labels} = “6”,multiply({Junior},376.04),(IF({Retailer#Labels} = “7”,multiply({Junior},325.25),(IF({Retailer#Labels} = “8”,multiply({Junior},292.73)))))))))))))))

There are already so many IFs and labels I have no idea how to add this other level to the formula. My knowledge on this is limited and now its become time sensitive, hopefully someone has the solution to help us.
Thank you!!!

Hi @marcepz - quick question, is there a common price change (%) that is consistent across the board for all products/customers or is in a different change for each?

Thanks
Mark

Hii @mark.anley! Its does vary by channel and retailer. For example in some channels bottles went from 331.08 to 374.13 (which is 7.24% I think), but on other channels price will vary by 7.43%.

In the first example I gave each of the SKUs changed by a specific % and we will actually be removing the labels to leave the formula as a simple: round(multiply({SKU1},331.034483),2).
I believe this is much simpler however, I still dont understand how to separate the formula before and after a date.

In the second example the changes do vary depending on contract. So RetailerA will have X% increase with SKU1 and Y% increase with SKU2, and so on and so forth for each retailer. What I thought could be done was this, but I am not even sure if it works that way:

**IF ‘date column’ is on or before 10/10/2022 (and show the complete formula as it is now), IF NOT (have the same nested formula but with the new prices) **

Hopefully this makes sense, I’'m still tryong to see if theres anything similiar in ny other thread.

@marcepz

First, your multi-retailer formula could be simplified with a syntax similar to this:
SWITCH({Retailer#Labels}, "1", multiply({Junior}, 336.36, "2",.... "8", multiply({Junior}, 292.73))

To then do the date condition:

IF(DAYS(FORMAT_DATE({Date 1}), "2022/10/10") <= 0, 
   SWITCH({Retailer#Labels}, "1", multiply({Junior}, 336.36, "2",...., "8", multiply({Junior}, 292.73)), 
   SWITCH({Retailer#Labels}, "1", multiply({Junior}, 888.88, "2",...., "8", multiply({Junior}, 999.73))
)

To add more:

IF(DAYS(FORMAT_DATE({Date 1}), "2022/10/10") <= 0,
   SWITCH({Retailer#Labels}, "1", multiply({Junior}, 336.36, "2",...., "8", multiply({Junior}, 292.73)), 
   IF(DAYS(FORMAT_DATE({Date 1}), "2023/1/31") <= 0,
      SWITCH({Retailer#Labels}, "1", multiply({Junior}, 888.88, "2",...., "8", multiply({Junior}, 999.73)),
      SWITCH({Retailer#Labels}, "1", multiply({Junior}, 1888.88, "2",...., "8", multiply({Junior}, 1999.73))
   )
)

Jim - The Monday Man
:magic_wand: Column Magic :sparkles:- the magical columns toolbox app

@JCorrell you’ve done it again, this worked like a charm!! Thank you so much for the input :raised_hands:

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.