Fiscal Year Creation

[#1]

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

[shubham_singh]

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

;

[#3]

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...

[shubham_singh]

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

[sdmech81]

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

Or just FY?

[#6]

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.