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
- Connect
- 1.3K Connectors
- 309 Workbench
- 7 Cloud Amplifier
- 10 Federated
- Transform
- 663 Datasets
- 119 SQL DataFlows
- 2.3K Magic ETL
- 823 Beast Mode
- Visualize
- 2.6K Charting
- 86 App Studio
- 46 Variables
- Automate
- 193 Apps
- 483 APIs & Domo Developer
- 87 Workflows
- 23 Code Engine
- AI and Machine Learning
- 23 AI Chat
- 4 AI Projects and Models
- 18 Jupyter Workspaces
- Distribute
- 117 Domo Everywhere
- 283 Scheduled Reports
- 11 Software Integrations
- Manage
- 143 Governance & Security
- 11 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 116 Community Announcements
- 5K Archive