Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

FISCAL YEAR

Can anyone share me a example qvw file on creating fiscal years?

1 Solution

Accepted Solutions
Highlighted
Creator II
Creator II

Re: FISCAL YEAR

I had created fiscal year in back end script like:

CASE WHEN TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) >=4 and TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) <=12
THEN CASE WHEN TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) >=1 and TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) <=3
THEN CONCAT(to_char(TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy'))-1),CONCAT('-',to_char(TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy')))))
ELSE CONCAT(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy'),CONCAT('-',to_char(TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy'))+1))) END
ELSE CONCAT(to_char(TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy'))-1),CONCAT('-',to_char(TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy'))))) END AS fiscal_year


Full Script:

XXTBZ_PURCHASE_URD_V:
LOAD "OPERATING_UNIT",
"ORGANIZATION_ID",
"ORGANIZATION_CODE",
"ITEM_CATEGORY",
PRODUCT,
PURITY,
"PRIMARY_UOM_CODE",
"ITEM_CODE",
"TRANSACTION_DATE",
"TRANSACTION_QUANTITY",
AMOUNT,
"TRANSACTION_TYPE_NAME",
"PUR_F_MONTH",
"F_YEAR",
"FISCAL_YEAR";

SQL SELECT
OPERATING_UNIT ,
ORGANIZATION_ID ,
ORGANIZATION_CODE ,
ITEM_CATEGORY ,
PRODUCT,
PURITY,
PRIMARY_UOM_CODE ,
ITEM_CODE ,
TRANSACTION_DATE ,
TRANSACTION_QUANTITY ,
AMOUNT,
TRANSACTION_TYPE_NAME,
to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm') as pur_F_Month,
to_char(TRANSACTION_DATE, 'yyyy') as F_Year,
CASE WHEN TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) >=4 and TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) <=12
THEN CASE WHEN TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) >=1 and TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) <=3
THEN CONCAT(to_char(TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy'))-1),CONCAT('-',to_char(TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy')))))
ELSE CONCAT(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy'),CONCAT('-',to_char(TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy'))+1))) END
ELSE CONCAT(to_char(TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy'))-1),CONCAT('-',to_char(TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy'))))) END AS fiscal_year


FROM DASH."XXTBZ_PURCHASE_URD_V";

 




 

View solution in original post

2 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: FISCAL YEAR

Find some of the useful links.

https://community.qlik.com/t5/QlikView-Documents/Fiscal-and-Standard-Calendar-generation/ta-p/148082...

https://community.qlik.com/t5/Qlik-Design-Blog/Fiscal-Year/ba-p/1472103

 

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
Creator II
Creator II

Re: FISCAL YEAR

I had created fiscal year in back end script like:

CASE WHEN TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) >=4 and TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) <=12
THEN CASE WHEN TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) >=1 and TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) <=3
THEN CONCAT(to_char(TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy'))-1),CONCAT('-',to_char(TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy')))))
ELSE CONCAT(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy'),CONCAT('-',to_char(TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy'))+1))) END
ELSE CONCAT(to_char(TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy'))-1),CONCAT('-',to_char(TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy'))))) END AS fiscal_year


Full Script:

XXTBZ_PURCHASE_URD_V:
LOAD "OPERATING_UNIT",
"ORGANIZATION_ID",
"ORGANIZATION_CODE",
"ITEM_CATEGORY",
PRODUCT,
PURITY,
"PRIMARY_UOM_CODE",
"ITEM_CODE",
"TRANSACTION_DATE",
"TRANSACTION_QUANTITY",
AMOUNT,
"TRANSACTION_TYPE_NAME",
"PUR_F_MONTH",
"F_YEAR",
"FISCAL_YEAR";

SQL SELECT
OPERATING_UNIT ,
ORGANIZATION_ID ,
ORGANIZATION_CODE ,
ITEM_CATEGORY ,
PRODUCT,
PURITY,
PRIMARY_UOM_CODE ,
ITEM_CODE ,
TRANSACTION_DATE ,
TRANSACTION_QUANTITY ,
AMOUNT,
TRANSACTION_TYPE_NAME,
to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm') as pur_F_Month,
to_char(TRANSACTION_DATE, 'yyyy') as F_Year,
CASE WHEN TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) >=4 and TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) <=12
THEN CASE WHEN TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) >=1 and TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) <=3
THEN CONCAT(to_char(TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy'))-1),CONCAT('-',to_char(TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy')))))
ELSE CONCAT(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy'),CONCAT('-',to_char(TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy'))+1))) END
ELSE CONCAT(to_char(TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy'))-1),CONCAT('-',to_char(TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'yy'))))) END AS fiscal_year


FROM DASH."XXTBZ_PURCHASE_URD_V";

 




 

View solution in original post