How do I use Fiscal Year in a Beast Mode?

mczerniak

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!

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!

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:

 

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!

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.