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.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 302 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 104 SQL DataFlows
- 633 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 760 Beast Mode
- 62 App Studio
- 42 Variables
- 699 Automate
- 181 Apps
- 457 APIs & Domo Developer
- 51 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 401 Distribute
- 116 Domo Everywhere
- 277 Scheduled Reports
- 8 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 111 Community Announcements
- 4.8K Archive