Show maximum week

Hi all,

I need to show the week number in the title of the table containing the data. I understand I’m not able to use anything else than plain regular text in the titles, right? In other tools I’d put something like: "Data from week " & MAX(weeknumber).

If I’m correct I should abuse the Insight and narrative for that. True? Then how will I be able to show the weeknumber? I have been able to show the latest day with “Maximum.timeValue.formattedValue”. But how can I edit this to show the week? Is there a function for that?

Thanks!

Hi LinkeLoutje,

You’ll need a couple of fields, but once you have that you can do a lot of different displays with insights.

First, if you want to take the Max time with the Maximum calculation will need to cast your timestamp field that you are taking data from into an integer. I would use UNIX time and take the average of that. This is because the maximum calculation looks for a value to calculate the maximum. If you want it to be the most recent time then that would be the UNIX time. Also, make sure to take the average because you could have more records on a previous day and that might actually sum up to be more than today.

2022-09-07 15_48_39-QuickSight Authoring

After that you can take what you were doing before and do: Data from week “Maximum.timeValue.formattedValue”

The date format is defaulted to day. However you can change the aggregation of the time field.

If you change the aggregation from Day to Week it will truncate you timestamp to the latest week!

image

Finally, if you want the number of the week you can go to format and select custom and type in w. See DateTimeFormat (Joda-Time 2.11.1 API) for supported formats.

Finally, you should get this as a result.

image

Please let me know if that answered you question.

Thanks!

1 Like

Hi, @LinkeLoutje. Did Max’s solution work for you? I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!

Thanks @Max ! Have not implemented the exact solution, but it has set me on the right track and has made me understand some more of Quicksight.

@Kristin. It does work for now, still I’d like to suggest adding the narrative feature to titles of visuals as well. How do you gather these suggestions? Due to the requirement that a dashboard should be usable on mobile devices I’m forced to use the tiled layout and having to create insights just to show the appropriate titles (with all kinds of layout issues) is just not the way to go.