Dates and time intervals
Hello,
I have a request from my sales team to implement a discount simulation in our sales analysis model.
To be more specific. I have a model with sales data by day/customer/product. In a different model account managers will input a discount rate by client and product with a validity date, meaning I will have a start date and an end date for a specific discount.
I need to make the link between the two models and find the corresponding discount to each sale based on the invoice date.
Here is an example of the structure of the models I have and the desired result.
I will be very greatful if someone can share an idea on how to impliment this.
Best,
Dumitrita
Answers
-
Great use case. One way to handle this is to "bin" the transaction data. Meaning for each account you will have a discount rate between a begin date and end date with a corresponding discount rate. But, to do this, you will need to follow the DISCO methodology, particularly, the "S" or System Module for your accounts and another for time, in this case, day. You'll also need a "C" module, or calculation module that allows you to lookup each transaction and determine which bin the transaction belongs to, in this case two dates. Here's a YouTube video that shows you how to bin, and a link to an article that shows you how step by step. Only drawback to this method is that your dates can overlap so make sure you add audits (or conditional formatting) to prevent this situation.
Step by Step Guide:
2 -
Thanks a lot for your contribution JaredDolich. Even though your solution is very interesting and will find many practical applications, it is not quite adapted to my use case. I have tried to adapt your solution to my specific case without success.
I will try to give some additional details and make a parallel with your solution hoping someone could share an idea on the implementation method.
I have a model with transactional sales data by invoice date, product, customer.
The discount I have to apply for a specific combination of client, product and invoice date has a start date and end date. In other words a product & client couple can have multiple invoice dates as well as multiple discount conditions.
In your exemple, on the other hand each product has a unique price and a single markdown sub item associated.
Let me give an example : I have client 1 product 1. If this client 1 is buying product 1 between 1 January and 2023 and 23 march 2023 it gets a 20% discount. however if the same client 1 is buying product 1 between the 15 mai and 28 august it gets 10% discount. In all other cases the client doesn't get any discount.
In conclusion, in my source data by client, product and invoice date I have to first look if there is a discount rule created. If there is a discount rule then based on the invoice date I have to identify which discount rate to apply to my sale.
Thanks in advance for any suggestions.
0
