Min & Max Dates for a 'Week Number' in a Pivot Table Row

I have a ‘syscreated’ date field in my raw data. I need my pivot table visual to broken down by months and weeks which I have done using, aggregating {syscreated} to (month) and using a formula for week number I found in this post. I would also like to reference the min and max dates that each {Week Number} relates to. Ideally I would just like it within a single field alongside {Week Number}, Or at worst, an extra row adjacent to the {WeekNumber] field. This is a pivot table visual that has rows and values and I will need it to go into the Rows area.

Any questions or issues and please just ask, many thanks.

Would something like this work?

max({date},[{Week Number}])

Hi @Max,

Thank you for your response.

I didn’t mention that I had tried that previously. With where I need to place it within my pivot table it brings back ‘Level aware calculation (LAC) aggregate expressions as dimensions is not supported. It is supposed to be used as a parameter of a visual aggregate function’.

Even at absolute worst case scenario (which stakeholders wouldn’t be happy with), adding it as a value (max) only works once all rows are expanded (there are different fields all within the rows section). This visual is most often only expanded out to the first 2-4 so would be a pain to have to expand it all completely just to see the value and then once you collapse the last field the value within the field just disappears.

Any further help would be greatly appreciated.

Many thanks.

Hi @riley.anderson - if I understand correctly what you are after, try something like this:

First calculate the week number (sounds like you did this already):
dateDiff(truncDate("WK", truncDate('YYYY', {order_date})), {order_date}, 'WK')+1

Then create a function which assembles a concatenated string of all the elements you want in the label (this will look like “Week X: 1/1/2023-1/7/2023”
concat('Week ', ifelse({Week Number}<10, '0',null), toString({Week Number}), ': ', formatDate(truncDate('WK', {order date}), 'MM/dd/yyyy'), '-', formatDate(addDateTime(6,'DD', truncDate('WK', {order date})),'MM/dd/yyyy'))


Note if you have more than 1 year of data in the table and dont have a column for Year in the pivot as a group by, you will need to change the date format in the calc above to be ‘yyyy/MM/dd’ in order for it to sort correctly since it is a string and is sorted alphabetically.

1 Like

Hi @Jesse,

Thank you for your response.

I can confirm that it does work. I just had to make some minor adjustments as I have my working week to start from a Monday as oppose to the Quicksight default of Sunday. I also did use ‘yyyy/MM/dd’ as I am UK based and it works better overall for ordering.

Thanks again.

1 Like

awesome - great to hear!!