How to show week end day instead of week start day while grouping data by week

Question & Answer
1

Hello,
I currently have a “Weekly” view of data in Pivot table visual and I have formatted the “date” in “Columns Field Wells” to show as “MMM DD”. This format by default shows the date as start day of the week. How do I get to show the ending date of the week instead as stakeholders are used to seeing Week end date in Excel WBR file? For example, in attached image, “yellow” color view is what I am able to get. How do I make changes to see the “green” color view of the date (or) is there any hack around it to make it look that way?

Week End Date

2

You could add a new field named {Week End Date} and use that in your visuals:

 addDateTime(6, 'DD', truncDate('WK', {date}))
3

Hi @darcoli The formula is throwing errors in QS. I fixed one error in truncDate and modified the formula to ‘addDateTime(6, ‘WW’, truncDate(‘WK’, {date}))’ but still getting below error. May I please know what does ‘WW’ represent here as I don’t see it in the valid periods on AWS documentation : addDateTime - Amazon QuickSight

“Expression addDateTime(6, ‘WW’, truncDate(‘WK’, {date})) for function addDateTime has incorrect argument type addDateTime(Number, String, Date). Function syntax expects Numeric, String, Date.”

4

Add 6 days to get the end week
Week End Saturday = ( addDateTime(6,'DD',truncDate('WK',{Order Date})) )

image

1 Like
5

My bad - had a mistake in the original post … edited now for reference. It’s basically what @Koushik_Muthanna said :wink:

6

Awesome. Thank you Koushik. That resolved it.

7

@darcoli Got it. Thank you very much for the help!!