Charting rolling n weeks based on week number selected in the dashboard filter
Hello, I have a weekly dataset with 'week number' as dashboard filter. I would like to create a chart showing rolling 8 weeks trend leading up to the week selected in the filter. For instance, if the user selects week 18, the chart should display weekly trend from Week 10 to week 18. How can I achieve this? Thank you!
Best Answers
-
Here's an example where you can use the Calendar reference dataset from the Domo Dimensions connector:
This example is getting 12 digits but you can change it to 8 if you want 8.
After the join I feed it into a formula tile to calculate the rolling date:
Date - Rolling 12 Months now has the date I'd use to visualize on the chart.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
@RanjitR Here you go
https://www.loom.com/share/94234f8644a84e1886b6d883e463aecd
John Le
You're only one dashboard away.
Click here for more video solutions:
0
Answers
-
You'd need to either define all of the selectable weeks in a variable (which you must keep manually updated every week)
or alternatively duplicate your data so that for every date you have that week and the past 8 weeks worth of data. This way when you filter on the selection date it'll show those prior weeks. You'd need to do this in an ETL. You can pull in the Date dimension dataset. Filter on a specific year and month and where the days are less than or equal to 8. Add a field to this dataset and your original dataset called "Join Column" with a value of 1. Then join these two together. Finall,y use a formula tile to calculate the new display date which you'll be filtering on:
`Date` - INTERVAL (`d` - 1) DAY
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Thank you @GrantSmith! Could you please expand more on "Filter on a specific year and month and where the days are less than or equal to 8"? How do I do this? An example would be great!
0 -
-
Hi @RanjitR ,
@GrantSmith is right in the approach. I think the question I have is do you always want it to be a rolling 8 weeks or do you need that to change?
John Le
You're only one dashboard away.
Click here for more video solutions:
0 -
Here's an example where you can use the Calendar reference dataset from the Domo Dimensions connector:
This example is getting 12 digits but you can change it to 8 if you want 8.
After the join I feed it into a formula tile to calculate the rolling date:
Date - Rolling 12 Months now has the date I'd use to visualize on the chart.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Thank you! I want to mention a few things here. My dataset follows fiscal year so the first day of the year isn't 01/01 and the week starts on Monday rather than Sunday. Additionally, I would need to use Week Number as my dashboard filter and not Date as suggested in the solution provided by @GrantSmith. Also, are you randomly selecting year and month in the filter tool or is there a logic behind it? I am attaching a sample dataset for you all? Thank you for your support! @GrantSmith @DashboardDude
0 -
@DashboardDude Is there any way you could show the solution in a video? A lot of users could benefit from this. I have attached a sample dataset in the previous comment. Thank you!
0 -
@RanjitR Here you go
https://www.loom.com/share/94234f8644a84e1886b6d883e463aecd
John Le
You're only one dashboard away.
Click here for more video solutions:
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 738 Beast Mode
- 57 App Studio
- 40 Variables
- 685 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive