Required help in writing the script

[kunkumnaveen]

Hello  All,

With the attached Calendar data ,i need to create two Dynamic Fiscal Year Dimensions as shown below 

if Q2 is selected it has to show Q2 of each  Fiscal Year ,and if Q3 is Selected it has to show Q3 of each Fiscal year and So on

Second is

last four Quarters 

I have converted those columns in to rows using cross table 

Mis:
crosstable(CalMonthYear,CalMonthYearValue,7)
LOAD

"Apr'16",
"May'16",
"Jun'16",
"Jul'16",
"Aug'16",
"Sep'16",
"Oct'16",
"Nov'16",
"Dec'16",
"Jan'17",
"Feb'17",
"Mar'17",
"Apr'17",
"May'17",
"Jun'17",
"Jul'17",
"Aug'17",
"Sep'17",
"Oct'17",
"Nov'17",
"Dec'17",
"Jan'18",
"Feb'18",
"Mar'18",
"Apr'18",
"May'18",
"Jun'18",
"Jul'18",
"Aug'18",
"Sep'18"

FROM [lib://AttachedFiles/MIS_CV.xlsx]
(ooxml, embedded labels, table is DATA);

MisRES:

load *,
num#('Q'&Ceil(Month(AddMonths(date(date#(PurgeChar(CalMonthYear,chr(39)),'MMMYY'),'MMYYYY'),9))/3)) as FiscalQuarter,

year(addmonths(date(date#(PurgeChar(CalMonthYear,chr(39)),'MMMYY'),'MMYYYY'),9)) as FiscalYear,

'F'&Chr(39)&right(year(addmonths(date(date#(PurgeChar(CalMonthYear,chr(39)),'MMMYY'),'MMYYYY'),9)),2) as FYear,

'F'&Chr(39)&right(year(addmonths(date(date#(PurgeChar(CalMonthYear,chr(39)),'MMMYY'),'MMYYYY'),9)),2)&' '&
'Q'&Ceil(Month(AddMonths(date(date#(PurgeChar(CalMonthYear,chr(39)),'MMMYY'),'MMYYYY'),9))/3) as FYearQuarter,

resident Mis;
Drop Table Mis;

 Any help in how to create those two requirements 

Thanks

[kunkumnaveen · Accepted solution]

I have able to achieve the second requirement ....
I need help in achieving the first requirement ...