How to Always Show the Most Recent Week with Data

Question & Answer
, , ,
1

Hello,

We’re looking for a way to dynamically show the most recent week of data in QuickSight, even when data for the latest calendar week is not available.

Right now, when we use a rolling date filter like “Start of last week – End of last week,” the dashboard shows no results if the data for that week hasn’t arrived yet. Instead, we’d like the visual to automatically fall back to the most recent week that does have data.

1193×836 71.1 KB

For Information - In our dataset, the Date field doesn’t follow a consistent pattern — some weeks have data for only a few days, others may have gaps or missing days entirely.

We want to configure our QuickSight visuals to automatically show the most recent week (starting Monday) that has any data.

image
image123×392 6.91 KB

We are looking to implement a visual-level solution for this issue.

Regards,
Nikhil.

1 Like
2

Hello @Nikhilburhade, we can handle this filtering through a calculated field rather than relying on the rolling dates within the filter. I also notice that you have filters for category, product, and retail so I will try to create a calculation that will dynamically update with that as well. I am not sure what metric field you are using in the visuals, so I will just put a default for now.

The calculation would look something like this:

ifelse(
    sumOver(
        ifelse(
            maxOver(truncDate("WK", {Date}), [], PRE_AGG) = truncDate("WK", {Date}), 
            {metric_value}, 
        NULL), 
    [], PRE_AGG) > 0 AND 
    truncDate("WK", {Date}), [], PRE_AGG) = truncDate("WK", {Date}) , 1,
    (sumOver(
        ifelse(
            maxOver(truncDate("WK", {Date}), [], PRE_AGG) = truncDate("WK", {Date}), 
            {metric_value}, 
        NULL), 
    [], PRE_AGG) = 0) OR
    isNull(sumOver(
        ifelse(
            maxOver(truncDate("WK", {Date}), [], PRE_AGG) = truncDate("WK", {Date}), 
            {metric_value}, 
        NULL), 
    [], PRE_AGG))) AND
    addDateTime(-1, "WK", maxOver(truncDate("WK", {Date}), [], PRE_AGG)) = 
    truncDate("WK", {Date}), 1,
0)

This may be a bit overcomplicated for the issue, but mainly, I wanted to check for the latest date and if there was data available for the date. This will find the max date week value, check the results, and go back a week if there isn’t any data present. Then, you can apply this as a filter on your visuals and just set it as a custom filter that equals 1 and exclude NULLs. Plus, since all of the calculations are PRE_AGG, the results will change depending on the other filters applied to the visuals. Let me know if this helps!

1 Like
3

Hello @DylanM,

Thank you very much for your response.

I tried creating the calculated field as per your suggestion, but we are encountering a syntax error. Could you please assist us in resolving this issue?

Thanks in advance for your help!

image
image708×421 13.1 KB

Regards,
Nikhil.

4

Hello @Nikhilburhade, you are missing the maxOver( argument on the line that is displaying the error. With that added in front of the truncDate function, it should work as expected. Also, if you are using {Store ID} as the field you are checking for, than you will need to change the sumOver functions to a distinctCountOver or countOver aggregation. It will not be able to sum an ID field so we need to check for values a different way.

1 Like
5

Hello @DylanM

Thank you so much for your help.
Instead of applying a direct try = 1 filter, I used an OR condition with the date filter, and we are now getting the expected results, as shown in the image below.

image
image1145×794 63.4 KB

Also do you think below formula will behave the same?

ifelse(
  truncDate("WK", Date) = 
    maxOver(
      ifelse(
        isNotNull({Store ID}),
        truncDate("WK", Date),
        NULL
      ),
      [],
      PRE_AGG
    ),
  1,
  0
)

Regards,
Nikhil.

1 Like
6

Hello @Nikhilburhade, I am glad we were able to get this working! From what I can tell, that calculated field you wrote above could also work! I am not positive just checking if Store ID is NULL will always resolve the issue, but it looks like it could work. Plus, that calculation looks a lot easier to manage than the one I suggested.

1 Like
7

Hello @DylanM

Thank you so much for the response.
Much appreciated !

Regards,
Nikhil.

1 Like