This Year vs Last Year with Date Grouping

[BrandonN]

Hi All,

Working on the Current Year vs Last year requirements but I got a request to group a certain date into the previous month it is mainly due to leap year Feb and It has a pre-defined calendar.
EG: 1 June 2019 should compare with 31 May 2021
Currently, the bar chart showing June and May.
Expected output the values should appear on May, is there any way recommend a solution in order to group certain date to the previous month? Every month will have a set of date grouping.

Thank you!

[RsQK]

Hey, try this:

TABLE:
LOAD * INLINE [
OrderID, Date,	Amount
1,	29/5/2020,	10
2,	30/5/2020,	20
3,	31/5/2020,	30
5,	1/6/2019,	22
];

temp:
LOAD
NUM(min_date+iterno()-1) as date
WHILE min_date+iterno()-1 <= max_date;

LOAD
MIN(YEARSTART(Date)) as min_date,
MAX(Date) as max_date
RESIDENT TABLE;

temp2:
LOAD
date,
YEAR(date) as year,
1 as counter
RESIDENT temp
ORDER BY date;

DROP TABLE temp;

temp3:
LOAD
date,
IF(PREVIOUS(year)=year,RANGESUM(PEEK(counter),counter),counter) as counter
RESIDENT temp2;

DROP TABLE temp2;

INNER JOIN (temp3)
LOAD
counter,
MAX(date) AS date_corr,
1 AS max_ym_flag
RESIDENT temp3
GROUP BY counter;

[ml_month_value]:
MAPPING LOAD
NUM(date),
MONTH(date_corr)
RESIDENT temp3;

DROP TABLE temp3;

Table2:
LOAD
*,
Month(Date) as Month,
Date(Date,'MMM-YYYY') as YearMonth,
APPLYMAP('ml_month_value',NUM(Date),Month(Date)) as Month_corrected
Resident TABLE;

Drop table TABLE;