How to show week-by-week comparisons in grouped vertical bar cards where the weeks roll-over years?
When I got into work this morning, the Grouped Vertical Bar cards which I had built to show week-by-week comparisons of THIS year's values versus LAST year's values were all broken.
The Beast Modes that I had created to differentiate by week were all malfunctioning because today is Wednesday, January 2, 2019, and the end of "Last" week was Monday, December 31, 2018.
The issue was that I needed to represent each week independently, and also sequentially, so that week 52 of 2018 will preceed week 1 of 2019.
The easiest "fix" that I could construct was to think of the week number for each year as a strictly numerical value -- rather than a string to be sorted -- so that they could be displayed left-to-right across the x-axis.
Here's a sample of the part of the Beast Modes that I created to accomplish this:
/* To compare the `date` of any row of data to that of the CURRENT_DATE() */
(abs(year(`date`))*52 + abs(weekofyear(`date`))) = (abs(year(CURRENT_DATE()))*52 + abs(weekofyear(current_date())) -2)
/* Accepting data where the date is from TWO Weeks ago! */
As you can see, each week number is calculated as the sum of the absolute value of the result of the "weekofyear" function plus 52 times the absolute value of the result of the "year" function - so that Dec 2018 is equal to 104948 internally, and Jan 2019 is likewise equal to 104949.
While any constant can be used in order to sort months BY ORDER, using 52 as the constant allows them be ordered SEQUENTIALLY.
I hope that this helps someone else as much as it helped me!
Respectfully,
Dean T. Pangelinan
Marketing Data Analyst – Technical Solutions, Global Marketing Analytics
Robert Half
Comments
-
Any Beast Mode gurus out there who want to chime in on this one?
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 602 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 695 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 113 Manage
- 110 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive