Charting rolling n weeks based on week number selected in the dashboard filter

RanjitR

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!

GrantSmith

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.

DashboardDude

@RanjitR Here you go

https://www.loom.com/share/94234f8644a84e1886b6d883e463aecd

GrantSmith

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

RanjitR

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!

RanjitR

@DashboardDude

DashboardDude

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?

GrantSmith

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.

RanjitR

Sample.xlsx

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

RanjitR

@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!

DashboardDude

@RanjitR Here you go

https://www.loom.com/share/94234f8644a84e1886b6d883e463aecd