FISCAL YEAR

[venkat1]

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

[kaushiknsolanki]

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

[venkat1 · Accepted solution]

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";