Find next value in table that matches specific criteria

[vkal12]

Hello Community,

the use case I am relating to has the following fields: Date, Category and Value. 

The date is always related to the current date (e.g. 2023-01-04). I need to create a new column "delivery date" that matches the following criteria: 

• For most of the categories (A, B) the delivery date is the current date + 1 day. In my example the delivery date will be the 2023-01-05. If the result of the current date + 1 day is a public holiday or a weekend then I need to get the next working day as delivery date. 
  Example: On Friday 2023-01-06 the delivery date for most categories should not be 2023-01-07 but 2023-01-09. 
• For specific categories (C, D) I have special requirements regarding the calculation of the delivery date. For those categories the delivery date is the current date + 2 days. In this case, weekends or public holidays should also be skipped and only the working days should be considered.

Attached please find an example: 

The table is only showing the case with weekends in between, not with public holidays. Basically I would need a lookup for the next working day in an extra table. How can I implement this in Qlik Sense? 

Thanks for your support in advance!

Kind regards,
Vanessa

[ogster1974]

This should give you a calendar to work with finding your next work date based on the date period of your data set.  Find your delivery date based on your category and you have enough information there via Apply map to determine if its 1. a non work day and 2. if it is when the next work day is.

HolidayTab:

load *, num(Holidate) as NumHolidate

Inline [Holidate, HolidayName

07/04/2012, Indepence day

09/03/2012, labor day

12/25/2012, X'mas

];

CalendarTmp:

LOAD Date, FlgNotWorkingDay,

Date(if(peek(FlgNotWorkingDay), peek(NextWorkingDay), peek(Date))) as NextWorkingDay;

LOAD

  Date,

  (FlgHoliday or FlgWeekend) as FlgNotWorkingDay;

LOAD

  Date,

  Exists('Holidate',Date) as FlgHoliday,

  Match(WeekDay(Date) ,5, 6) as FlgWeekend;

LOAD

  Date(Today()+30-Recno()+1) as Date

AUTOGENERATE Today()+30 - MakeDate(2012, 7, 1) + 1;

[vkal12]

Hi ogster1974,

thank you very much for the example and explanation! I have created an external calender that I've loaded into Qlik Sense. This calendar looks like this: 

Similar like in your example I flagged "NotWorkingDays" with -1 and "WorkingDays" with 0, depending on whether it is a weekend or a public holiday. 

At the moment I am stuck on how to use the Peek()-function now to generate the column "NextWorkingDay". How did you get this and where does it come from? 

Thank you very much for your support!