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.
Riley
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.
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.
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.
