First day of the week
Hello. I have an excel output which has data fed everyday. that contains all information about how many sales we made everyday. I would like to put on a dashboard that gives ne weekley information. So how can I code in magic ETL/ Beat Mode. So I can make my first day of the week as Sunday. eg: August 25th 2019 to August 31st as my week. So how can I automate or code for 52 weeks.
Comments
-
HI - I would probably use another table which contains all the date info and join to your daily sales data using the date key. The date table will probably look like this. So 25 Aug - 31 Aug 2019 will belong to week 35.
Alternatively, in beast mode, try WEEKOFYEAR(`Date`)
0 -
Please elaborate, how can I code this in SQL? If I understood it correctly did you mean, I make one separate sheet which contains only date of sales and then join that to whole data. .But how do I code in SQL?
0 -
Hi there - Assume your daily data looks like this
Daily Data
And you set up your Dim Date as below. Note the WeekNoCustom column at the end
Dim Date
Then join both datasets using Magic ETL. You may want to change the WeekNoCustom's data type to string to make it easy.
Summarize by WeekNoCustom in Analyzer
Analyzer
Hope that helps ?
0 -
I have had a similar issue where I had daily snapshots, and when I graphed by week they would all aggregate and show incorrect numbers. To get around this, I created a Beastmode filter at the card to only include snapshots on the first day of the week (see attached). Then, on the card, I filtered on this calculated field to only include 'Weekly Snapshot'
I hope this helps!
0 -
Thanks for explaining it so nicely, appreciate it !
I am curious as if I split the dim date, how will it get automated then? As all the data is sitting in one spreadsheet only. As this sales information will be coming everyday, the scheduled file is updated everyday. How will Dim date get updated then ?
0 -
Hi - You can set up the dim date in various ways:-
1) append one year of data to the dim date (manual every year)
2) append 100 years of data to the dim date (manual one off)
3) write a SQL procedure to populate the dim date (auto yearly)
The magic ETL that does the join will simply take the daily sales data ( that gets updated every day and joins to the dim Date ). You can set the ETL to run whenever the sales spreadsheet gets updated.
As your weekNo requirement is different from the built-in function (WeekOfYear(date)), you need to do a customWeekNo. Otherwise, you need to use some complex rules in the beast mode
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