A chart is providing a period over period comparison across same months from current and previous years.
Now when I open the chart Today(Jan 2022) I need to show previous 12 months i.e From Jan 2021 till Dec 2021 comparing with Jan 2020 till Dec 2020. This is happening, but now I need to sort the months i.e when I open the same chart suppose in Feb 2022, the chart should show comparison From
Feb 2021- Jan 2022 to Feb 2020-Jan 2021.
I am using Table calculations as follow to do the sorting
MonthNumber =
case (
when (${invoice.invoice_posted_month_name}="January",1),
when (${invoice.invoice_posted_month_name}="February",2),
when (${invoice.invoice_posted_month_name}="March",3),
when (${invoice.invoice_posted_month_name}="April",4),
when (${invoice.invoice_posted_month_name}="May",5),
when (${invoice.invoice_posted_month_name}="June",6),
when (${invoice.invoice_posted_month_name}="July",7),
when (${invoice.invoice_posted_month_name}="August",8),
when (${invoice.invoice_posted_month_name}="September",9),
when (${invoice.invoice_posted_month_name}="October",10),
when (${invoice.invoice_posted_month_name}="November",11),
when (${invoice.invoice_posted_month_name}="December",12),
0
)
And
SortOrder =
case(
when(extract_months(now()) <= ${monthnumber},-12 - (extract_months(now())-${monthnumber})),
${monthnumber}
)
but now able to get the required result, Can somebody help. Thanks