How do I use Fiscal Year in a Beast Mode?

I'm looking to create a case statement to indicate the fiscal year that a transaction occured in. Here is what I have so far:

case when `SHIP_DATE` between '06/01/2014' and '05/31/2015' then 'FY15'
when `SHIP_DATE` between '06/01/2015' and '05/31/2016' then 'FY16'
when `SHIP_DATE` between '06/01/2016' and '05/31/2017' then 'FY17'
when `SHIP_DATE` between '06/01/2017' and '05/31/2018' then 'FY18'
when `SHIP_DATE` between '06/01/2017' and '05/31/2019' then 'FY19'
else 'null' end

The error I see is a syntax error, so can anyone see where I went wrong? Thank you!

Find more posts tagged with

Accepted answers

mczerniak

@KVincent, thanks for your help. I had continued to investigate, and came up with this:

case when `SHIP_DATE` >= '2014-06-01' and `SHIP_DATE` <= '2015-05-31' then 'FY15'
when `SHIP_DATE` >= '2015-06-01' and `SHIP_DATE` <= '2016-05-31' then 'FY16'
when `SHIP_DATE` >= '2016-06-01' and `SHIP_DATE` <= '2017-05-31' then 'FY17'
when `SHIP_DATE` >= '2017-06-01' and `SHIP_DATE` <= '2018-05-31' then 'FY18'
when `SHIP_DATE` >= '2018-06-01' and `SHIP_DATE` <= '2019-05-31' then 'FY19'
else 'null' end

Much appreciated!

All comments

KVincent

Here is the code that I would use let me know if this doesnt work

(case when `SHIP_DATE` >= '06/01/2014' and ` SHIP_DATE` <= '05/31/2015' then 'FY15'
when when `SHIP_DATE` >= '06/01/2015' and ` SHIP_DATE` <= '05/31/2016' then 'FY16'
when when `SHIP_DATE` >= '06/01/2016' and ` SHIP_DATE` <= '05/31/2018' then 'FY17'
when when `SHIP_DATE` >= '06/01/2017' and ` SHIP_DATE` <= '05/31/2018' then 'FY18'
when when `SHIP_DATE` >= '06/01/2018' and ` SHIP_DATE` <= '05/31/2019' then 'FY19'
else 'null' end)

mczerniak

@KVincent, thanks for your help. I had continued to investigate, and came up with this:

Much appreciated!

rado98

Ideally you shouldn't even have to go through that exersice. As long to you have your fiscal calendar enabled on your instance it should be done automatically.