Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

kunkumnaveen
Valued Contributor

Required help in writing the script

Hello  All,

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

Capture1.PNG

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

Capture2.PNG

 

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

 

 

 

1 Solution

Accepted Solutions
kunkumnaveen
Valued Contributor

Re: Required help in writing the script

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

View solution in original post

1 Reply
kunkumnaveen
Valued Contributor

Re: Required help in writing the script

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

View solution in original post