This Year vs Last Year with Date Grouping

BrandonN — Tue, 17 Aug 2021 05:26:45 GMT

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!

Re: This Year vs Last Year with Date Grouping

RsQK — Tue, 17 Aug 2021 12:09:45 GMT

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;

topic Re: This Year vs Last Year with Date Grouping in App Development

This Year vs Last Year with Date Grouping

Re: This Year vs Last Year with Date Grouping