How do I use Fiscal Year in a Beast Mode?

mczerniak
mczerniak
in Magic ETL

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!

Best Answer

  • mczerniak
    mczerniak
    Answer ✓

    @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!

Answers

  • KVincent
    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
    mczerniak
    Answer ✓

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

Categories