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!
Best 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' endMuch appreciated!
1
Answers
-
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)0 -
@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' endMuch appreciated!
1 -
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.
0
Categories
- All Categories
- Product Ideas
- 2.1K Ideas Exchange
- Data Connections
- 1.3K Connectors
- 309 Workbench
- 17 Cloud Integrations
- Data & ETL
- 2.3K Magic ETL
- 120 SQL DataFlows
- 666 Datasets
- 827 Beast Mode
- Visualize & Apps
- 90 App Studio
- 198 Pro-code Components
- 2.6K Charting & Analyzer
- 46 Calculations & Variables
- AI & Data science
- 23 Domo AI & AI Chat
- 4 Managing AI
- 18 Jupyter Workspaces
- 122 Workflows
- Distribute
- 118 Domo Everywhere
- 284 Reporting
- Manage
- 145 Governance & Security
- 489 APIs
- 11 Add-ins & Plugins
- 13 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 117 Community Announcements
- Automate
- 5K Archive