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

Question & Answer
,
4

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'))

image

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