How do I use Fiscal Year in a Beast Mode?

mczerniak
mczerniak Member
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 Member
    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 Member

    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 Member
    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 Contributor

    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