Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
MatheusC
Specialist
Specialist

Holidays for the next business day of sale

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

Did you find a solution to your question? Mark the solution as accepted and if you found it useful, press the like button!
Labels (2)
1 Solution

Accepted Solutions
Or
MVP
MVP

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.

 

View solution in original post

3 Replies
Or
MVP
MVP

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
Specialist
Specialist
Author

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

Did you find a solution to your question? Mark the solution as accepted and if you found it useful, press the like button!