topic Re: Fiscal Year Creation in QlikView

Fiscal Year Creation

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I'm bringing in data from an xlsx format with one of the fields for Month. The date format is M/D/YYYY. I would like to create a master calendar for fiscal year identification where my fiscal year identifies dates between 10/1/2014 - 9/30/2015 as FY15, 10/1/2015-9/30/2016 as FY16. How can I do this?

Thank you,

Bao Nguyen

Re: Fiscal Year Creation

shubham_singh — Tue, 07 Feb 2017 18:41:17 GMT

Assuming column name in Excel is RawDate

use this for calculating Year

LOAD

RawDate,

'FY'&(Year(YearStart(Date#(RawDate,'M/D/YYYY'),1,10))-2000) as FiscalYear

;

Re: Fiscal Year Creation

Tue, 07 Feb 2017 19:32:36 GMT

Hi Shubham,

I followed your sample from above but added an IF statement because I wanted to cover a range of date for each fiscal year. However, I'm getting an error message and not sure why. Can you help?

LOAD

Month,

"2015" & If(Month >= (10/1/2014) AND Month <= (9/30/2015)) as FiscalYear

FROM...

Re: Fiscal Year Creation

shubham_singh — Wed, 08 Feb 2017 06:12:59 GMT

The code I wrote will give you your desired Fiscal Year,

as far as your code is concerned, it is wrong,

you only write field names in double quotes and you can't compare Month with Date

Re: Fiscal Year Creation

sdmech81 — Wed, 08 Feb 2017 06:30:14 GMT

Do u want master calendar based ur start n end dates?

Or just FY?

Re: Fiscal Year Creation

Thu, 09 Feb 2017 13:38:56 GMT

I would like to have a master calendar to designate our fiscal year to start on 10/1/YYYY and ends on 9/30/YYYY. I was able to do this in SQL with another BI but cannot replicate it in Qlik...as my knowledge of Qlik has not yet matured enough at this point. Thanks.