Holidays for the next business day of sale

[MatheusC]

Hi, 

I have a calendar with national/state/municipal holidays. Working days are created from it.

My problem is that there can be sales on holidays and then it doesn't appear as a business day. My solution is to transfer this sale to T+1 (one day after this holiday).

example:
// create a calendar mapping and fetch the result with applymap from holiday dates and add D+1 to the date.
if(ApplyMap('MappingHolidays',"Sale-Date")="Sale-Date",Date("Sale-Date"+1),"Sale-Date")
--solution found

However, this is not always the case, when transferring the sale to the next day, it may fall on a Saturday or transfer to Monday and Monday may be a holiday, etc...

I'll leave an example of my tables, calendars and sales.

Table_Holidays:
load * Inline [
store, Date, holiday Name, Holiday
store1, 11/15/2023, holiday name, national
store2, 11/15/2023, holiday name, national
store3, 11/15/2023, holiday name, national
store4, 11/15/2023, holiday name, national
store1, 11/20/2023, holiday name, municipal
store2, 11/09/2023, holiday name, state
store3, 11/09/2023, holiday name, state
];

_________________________________________________________________________________________________

Sales_Table:
load * Inline [
store, DateSale, valueSale
store1, 11/15/2023,1212
store2, 11/15/2023,2131
store3, 11/15/2023,5132
store4, 11/15/2023,56413
store1, 11/20/2023,13216
store1, 11/16/2023,5000
store2, 11/16/2023,3000
store3, 11/16/2023,8000
store4, 11/16/2023,60000
other fields.....
];

Thank you in advance for sharing your ideas

Best regards
Matheus

[Or · Accepted solution]

Have a look at using LastWorkDate()or FirstWorkDate() to create your mapping table, instead of a straight +1. You could also use these directly in your load and skip the mapping outright if you prefer.

[marcus_sommer]

I think I would tend to prepare the master-calendar appropriately. This means beside the date-key is a field which contained the normal date for these business-dates and the next business-date for any weekend/holidays (maybe defined by interrecord-functions in forward + backward running loads) - and then just using this field where it is needed.

This would avoid touching the facts with any mapping as well as being able to show the real truth and not a manipulated version. 

[MatheusC]

Thanks @marcus_sommer @Or  for sharing your ideas.

@Or  I managed to apply the function LastWorkDate() in my case, it worked for what I needed.

Att, Matheus