Inlude Null Values on Date Axis

I’m trying to get a visual to show the previous 9 weeks of a measure but as the first week and last few weeks have no data, these weeks don’t even show up.

The chart should be showing a week before, and a few weeks after the weeks being shown. Is it possible to show these missing weeks?

Thanks!

Hi @RMB. Try this and let us know if it helps.

In QuickSight, if you want to show the previous 9 weeks of a measure, including weeks with no data, you can follow these steps:

  1. Create a calculated field to generate a list of dates for the previous 9 weeks. You can use the dateadd function to subtract the number of weeks from the current date. For example, the following formula generates a list of dates for the previous 9 weeks:

dateadd(‘week’, -9, now())

  1. Create a dataset or modify your existing dataset to include all the weeks you want to display, even if they have no data. You can use the calculated field from step 1 to generate a list of dates.
  2. Create a visual, such as a chart, using the modified dataset. Ensure that the date field is used as the X-axis or dimension.

By including the weeks with no data in your dataset, QuickSight should display those weeks on the chart, even if there is no corresponding data. This way, you can visualize the previous 9 weeks, including the weeks with no data.

If you need further assistance or have more specific questions, please feel free to ask.

2 Likes

Hi @RMB Did @rickm’s solution answer your question? If so, could you help the community out by clicking the check box under the reply to mark it as solved? Thanks!