Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Next given weekday's date

Hello!

I would like to make some adjustments to my date.

     

Orig DateOrig Date Weekday NumOrig Date WeekdayPeriod Format Weekday NumPeriod Format Weekday
2.2.20151Mo1Mo
14.10.20154We2Tu
5.5.20152Tu3We
3.4.20155Fr4Th
1.6.20151Mo5Fr

I have to calculate a new date base on the "Period Format" information. If weekday number of original date is not matching with the weekday number of "Period format" I need to move the date to the next working date according to the weekday number of "Period format"

Thus,

14.10.2015 (Wednesday) should be recalculated and result in next Tuesday (20.10.2015).

5.5.2015 (Tuesday) to become next Wednesday (6.5.2015).

If weekdays of both "Orig.Date" and "Period Format" are matching the date is not recalculated. This is an easy part but I have some problems in implementing the formula for the condition when weekdays are non-matching. Could anyone provide any hints please?

Tags (2)
1 Reply

Re: Next given weekday's date

May be this:

Table:

LOAD *,

  Date(If([Orig Date Weekday Num] = [Period Format Weekday Num], [Orig Date],

  If([Orig Date Weekday Num] > [Period Format Weekday Num], [Orig Date] + 7 - ([Orig Date Weekday Num] - [Period Format Weekday Num]),

  [Orig Date] - ([Orig Date Weekday Num] - [Period Format Weekday Num])))) as New_Date;

LOAD * INLINE [

    Orig Date, Orig Date Weekday Num, Orig Date Weekday, Period Format Weekday Num, Period Format Weekday

    2.2.2015, 1, Mo, 1, Mo

    14.10.2015, 3, We, 2, Tu

    5.5.2015, 2, Tu, 3, We

    3.4.2015, 5, Fr, 4, Th

    1.6.2015, 1, Mo, 5, Fr

];

I assumed that there was a typo on the second row where you have Orig Date Weekday Num = 4 for We and fixed it in my sample to get the right New_Date

Capture.PNG