Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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