Determine the Week of the month in Magic ETL
I'm running a sales forecast dataset and want to be able to determine what week of the month we are currently in so I can calculate the weekly sales forecast target.
Such as if the month target is 100,000 and its week 1 then the weekly target should be 25,000.
I can add a date calculation column that gives me the Week Year for the current day but I can't figure out how I can use the week year without building out an entire Week Year Calendar dataset to pull in which I'd really rather not do.
Let me know if that is my only option or if anyone has any other ideas.
Thanks
Randy
Best Answer
-
Hey @Randyb,
I don't know that a solution exists in Magic ETL, but I have found a solution that seems to work fairly well in Beast Mode:
FLOOR((DAYOFMONTH(`Date`) - 1) / 7) + 1
I know it's not the solution you asked for, but hopefully it will provide a workaround.
Best of luck!
**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'0
Answers
-
Hey @Randyb,
I don't know that a solution exists in Magic ETL, but I have found a solution that seems to work fairly well in Beast Mode:
FLOOR((DAYOFMONTH(`Date`) - 1) / 7) + 1
I know it's not the solution you asked for, but hopefully it will provide a workaround.
Best of luck!
**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'0 -
Thanks, I'll do some investigating, I was really hoping to stay away from the Beast Mode approach as this was going to support another calculation that I wanted to keep in the dataset.
I may have to be willing to compromise a bit as well.
Appreciate the advise, will advise as to what I end up doing
Randy
0 -
I ended up using it and it worked fine, gave me a little more flexibility as well, thanks for your help
Randy
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive