topic Re: FISCAL YEAR in QlikView https://community.qlik.com/t5/QlikView/FISCAL-YEAR/m-p/1681650#M727789 <P>I had created fiscal year in back end script like:<BR /><BR />CASE WHEN TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) &gt;=4 and TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) &lt;=12<BR />THEN CASE WHEN TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) &gt;=1 and TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) &lt;=3<BR />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')))))<BR />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<BR />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<BR /><BR /><BR />Full Script:</P><P>XXTBZ_PURCHASE_URD_V:<BR />LOAD "OPERATING_UNIT",<BR />"ORGANIZATION_ID",<BR />"ORGANIZATION_CODE",<BR />"ITEM_CATEGORY",<BR />PRODUCT,<BR />PURITY,<BR />"PRIMARY_UOM_CODE",<BR />"ITEM_CODE",<BR />"TRANSACTION_DATE",<BR />"TRANSACTION_QUANTITY",<BR />AMOUNT,<BR />"TRANSACTION_TYPE_NAME",<BR />"PUR_F_MONTH",<BR />"F_YEAR",<BR />"FISCAL_YEAR";<BR /><BR />SQL SELECT<BR />OPERATING_UNIT ,<BR />ORGANIZATION_ID ,<BR />ORGANIZATION_CODE ,<BR />ITEM_CATEGORY ,<BR />PRODUCT,<BR />PURITY,<BR />PRIMARY_UOM_CODE ,<BR />ITEM_CODE ,<BR />TRANSACTION_DATE ,<BR />TRANSACTION_QUANTITY ,<BR />AMOUNT,<BR />TRANSACTION_TYPE_NAME,<BR />to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm') as pur_F_Month,<BR />to_char(TRANSACTION_DATE, 'yyyy') as F_Year,<BR />CASE WHEN TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) &gt;=4 and TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) &lt;=12<BR />THEN CASE WHEN TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) &gt;=1 and TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) &lt;=3<BR />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')))))<BR />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<BR />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</P><P><BR />FROM DASH."XXTBZ_PURCHASE_URD_V";</P><P>&nbsp;</P><P><BR /><BR /><BR /></P><P>&nbsp;</P> Wed, 04 Mar 2020 11:58:12 GMT venkat1 2020-03-04T11:58:12Z FISCAL YEAR https://community.qlik.com/t5/QlikView/FISCAL-YEAR/m-p/1679280#M727787 <P>Can anyone share me a example qvw file on creating fiscal years?</P> Wed, 25 Nov 2020 16:16:04 GMT https://community.qlik.com/t5/QlikView/FISCAL-YEAR/m-p/1679280#M727787 venkat1 2020-11-25T16:16:04Z Re: FISCAL YEAR https://community.qlik.com/t5/QlikView/FISCAL-YEAR/m-p/1679295#M727788 <P>Find some of the useful links.</P><P><A href="https://community.qlik.com/t5/QlikView-Documents/Fiscal-and-Standard-Calendar-generation/ta-p/1480822" target="_blank">https://community.qlik.com/t5/QlikView-Documents/Fiscal-and-Standard-Calendar-generation/ta-p/1480822</A></P><P><A href="https://community.qlik.com/t5/Qlik-Design-Blog/Fiscal-Year/ba-p/1472103" target="_blank">https://community.qlik.com/t5/Qlik-Design-Blog/Fiscal-Year/ba-p/1472103</A></P><P>&nbsp;</P> Wed, 26 Feb 2020 07:26:15 GMT https://community.qlik.com/t5/QlikView/FISCAL-YEAR/m-p/1679295#M727788 kaushiknsolanki 2020-02-26T07:26:15Z Re: FISCAL YEAR https://community.qlik.com/t5/QlikView/FISCAL-YEAR/m-p/1681650#M727789 <P>I had created fiscal year in back end script like:<BR /><BR />CASE WHEN TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) &gt;=4 and TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) &lt;=12<BR />THEN CASE WHEN TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) &gt;=1 and TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) &lt;=3<BR />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')))))<BR />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<BR />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<BR /><BR /><BR />Full Script:</P><P>XXTBZ_PURCHASE_URD_V:<BR />LOAD "OPERATING_UNIT",<BR />"ORGANIZATION_ID",<BR />"ORGANIZATION_CODE",<BR />"ITEM_CATEGORY",<BR />PRODUCT,<BR />PURITY,<BR />"PRIMARY_UOM_CODE",<BR />"ITEM_CODE",<BR />"TRANSACTION_DATE",<BR />"TRANSACTION_QUANTITY",<BR />AMOUNT,<BR />"TRANSACTION_TYPE_NAME",<BR />"PUR_F_MONTH",<BR />"F_YEAR",<BR />"FISCAL_YEAR";<BR /><BR />SQL SELECT<BR />OPERATING_UNIT ,<BR />ORGANIZATION_ID ,<BR />ORGANIZATION_CODE ,<BR />ITEM_CATEGORY ,<BR />PRODUCT,<BR />PURITY,<BR />PRIMARY_UOM_CODE ,<BR />ITEM_CODE ,<BR />TRANSACTION_DATE ,<BR />TRANSACTION_QUANTITY ,<BR />AMOUNT,<BR />TRANSACTION_TYPE_NAME,<BR />to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm') as pur_F_Month,<BR />to_char(TRANSACTION_DATE, 'yyyy') as F_Year,<BR />CASE WHEN TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) &gt;=4 and TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) &lt;=12<BR />THEN CASE WHEN TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) &gt;=1 and TO_NUMBER(to_char(to_date(TRANSACTION_DATE, 'DD-MM-YYYY'), 'mm')) &lt;=3<BR />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')))))<BR />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<BR />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</P><P><BR />FROM DASH."XXTBZ_PURCHASE_URD_V";</P><P>&nbsp;</P><P><BR /><BR /><BR /></P><P>&nbsp;</P> Wed, 04 Mar 2020 11:58:12 GMT https://community.qlik.com/t5/QlikView/FISCAL-YEAR/m-p/1681650#M727789 venkat1 2020-03-04T11:58:12Z