Here is my problem:
I have 1 year of data, but one of the key criteria, rate per miles, is updated three times during the year. If you can imagine, from January to March we would charge $1.00, from March to September we would charge $1.25 and from September til year-end, we would charge $1.30. Updates were not made so uniformly however (such that the rate changes, and months of change differed). I created a formula which, based on some characteristics of the data, identifies which rows to keep, remove or overwrite.
My dataset would have data points for an April trip at $1.00, despite the rate having changed to $1.25 back in March. I had an identifier in the dataset which called out whether a rate was updated, and, if so, when the update happened. If the contract's start date lines us with the expected start date, the item is given a "Keep" identifier. If an update occurred before the month of accrual for the datapoint, the row should be dropped.
I was able to get rid of all the 'Remove' items with a filter tool, but am unsure how to get rid of the 'Keep' items which are obsolete due to the update.
Does anyone have any ideas?
Could you share you formula in the post? because without context to the formula (where I assume it goes wrong) This is a hard question to solve 🙂
The formula is quite simple, here it is:
IF [policy inception date]=[date] THEN "Keep" ELSEIF [Month_Accrual] >= [date] THEN "Overwrite" ELSE "Remove" ENDIF
Note:
[date] is the date of the contract, so if it was updated, that would be reflected in the date. The inception date is a constant, and represents the start of a policy period. The beginning of the formula acknowledges that if a policy's "update date" is the same as its start date, then no update has happened on that row of data.
Another way I am thinking about this problem is, that if I have two rows of data, where every single criteria is identical EXCEPT for the overwrite identifier, I'd drop the non-overwrite item and keep the overwrite item.