Last 5 Working Days.

[Satish_111]

Hi Team,

There Have two tables

1) Table- Working_Days.(In a table only Working Days dates, not included Week ends (SUN,SAT)).

2) Table- Holydays (In a table only holiday dates ).

When I select any single date one filter pane only last five days should come, Excepted public holidays and weekend's? Then Sum(Closing_Price) based on filter.

Please Explain both Script And front-end.

WorkingDays:
Load * inline [
Working_Days, Closing_Price
02/12/2024,10
03/12/2024,20
04/12/2024,30
05/12/2024,40
06/12/2024,50

09/12/2024,60
10/12/2024,70
11/12/2024,80
12/12/2024,65
13/12/2024,90

16/12/2024,41
17/12/2024,102
18/12/2024,103
19/12/2024,104
20/12/2024,105

23/12/2024,106
24/12/2024,7
25/12/2024,8
26/12/2024,9
27/12/2024,10

30/12/2024,11
31/12/2024,22
];

Holydays:
Load * Inline [
Holydays_Dates
25/12/2024
12/12/2024

];

Thank,

Satish

[PradeepK]

Hi, Please share one example of the selection and required result.  Let's say if 31/12/2024 is selected then what date values should be considered for Price calculation..

Previous 5 days ?

23/12/2024,106
24/12/2024,7
25/12/2024,8    // Holiday - omitted 
26/12/2024,9
27/12/2024,10

30/12/2024,11

[Satish_111]

First example:- I will selected date 31/12/2024 then last five working
dates 31/12/2014, 30/12/2014, 27/12/2014, 26/12/2014, 23/12/2024 ?//
Because 29/12/2024,28/12/2014(Weekends holiday (Sun, SAT) ) and
25/12/2024(christmas holiday).

2) second example:- I will selected date 20/12/2024 then last five working
dates,
20/12/2024, 19/12/2024, 18/12/2024, 17/12/2024, 16/12/2024 ?//Because
bcause this week no holidays

[PradeepK]

I Hope this helps 😄

Basic Logic

1 - Do Outer Join to make all combination

2 - Filter Weekends and Holidays

3 - Filter windowSize i.e. 5 Dates 

/* CODE */

//===============================================
// Init Variables
//===============================================

SET FirstWeekDay=0;
Let vStartDate = num(Date#('01/12/2024','DD/MM/YYYY'));
Let vEndDate = num(MonthEnd($(vStartDate)));
Let vWindoSize = 5;

//===============================================
// Generate Data - Sales and Holiday
//===============================================

Sales:
LOAD
 salesDate,
    floor( Rand() * 100 * IterNo()) as Sales,
    WeekDay(salesDate) as day
WHERE  NOT MATCH(num(WeekDay(salesDate)), 5 , 6);

LOAD
 Date($(vStartDate) + IterNo() - 1) as salesDate
AutoGenerate 1
WHILE $(vStartDate) + IterNo() - 1 <= $(vEndDate);

Holiday:
LOAD * Inline ` 
 holidayDate
 25/12/2024
 12/12/2024
`;

//===============================================
// Filter Data - Remove Holiday and Weekends
//===============================================

FilterTable_Temp:
LOAD
 salesDate as filterDate 
RESIDENT Sales
Where not Exists(holidayDate,salesDate); //Valid Sales Date

OUTER JOIN (FilterTable_Temp)

LOAD
 filterDate as salesDate
RESIDENT FilterTable_Temp;

NoConcatenate

FilterTable:
LOAD 
 salesDate,
 filterDate,
 AutoNumber(RowNo(),filterDate) as RANK
RESIDENT FilterTable_Temp
WHERE salesDate <= filterDate
ORDER BY filterDate, salesDate DESC;

NoConcatenate

/* Keep Only last 5 Records */
Link:
LOAD
 salesDate,
 filterDate
RESIDENT FilterTable
WHERE RANK <= $(vWindoSize);

DROP TABLE FilterTable_Temp,FilterTable;