lastWeekOfMonth

lastWeekOfMonth(X, [weekday=0], [offset], [n=1])

X is a scalar/vector of type DATE, DATETIME, TIMESTAMP or NANOTIMESTAMP.

weekday is an integer from 0 to 6. 0 means Monday, 1 means Tuesday, ..., and 6 means Sunday. The default value is 0.

offset is a scalar of the same data type as X. It must be no greater than the minimum value of X. The default value is the minimum value of X.

n is a positive integer. The default value is 1.

When both offset and n are specified and n > 1, the function calculates dates based on periods consisting of n months. In this case, offset determines how the multi-month periods are aligned. Specifically, the system first calculates the date corresponding to the last occurrence of the specified weekday in the month containing offset, and uses that date as the reference point. The result is then updated every n months, and the function returns the target date corresponding to the period to which X belongs.

lastWeekOfMonth(2019.11.24,2);
// output: 2019.10.30
// The last Wednesday of November 2019 is 2019.11.27, 2019.11.24 is before 2019.11.27, so the function returns the last Wednesday of October 2019.

lastWeekOfMonth(2019.11.29,2);
// output: 2019.11.27

date=2012.01.02 2012.02.03 2012.03.07 2012.04.08 2012.05.12 2012.06.16 2012.07.18 2012.08.20 2012.09.25 2012.10.28
time = [09:34:07,09:36:42,09:36:51,09:36:59,09:32:47,09:35:26,09:34:16,09:34:26,09:38:12,09:38:13]
sym = take(`MSFT,10)
price= 49.6 29.46 29.52 30.02 174.97 175.23 50.76 50.32 51.29 52.38
qty = 2200 1900 2100 3200 6800 5400 1300 2500 8800 4500
t1 = table(date, time, sym, qty, price);

select avg(price),sum(qty) from t1 group by lastWeekOfMonth(date,4,2012.01.01,2);