Drill down from Week to Day

[Asfak]

Hi Team, I would like to achieve below in quicksight especially the drill down option. At the moment im able to achieve reporting based on weekending date but unable to drill down to day.

• Week Definition: Weeks should start on Monday and end on Sunday.
• Week Representation: Each week should be represented by its ending date (e.g., “Week Ending 17-Nov-2024”).
• Drill-Down Functionality:
• Week Level: Show total quantity for the entire week- week ending 17-Nov-2024
• Day Level: Show daily quantity for each day within the selected week ending 17-Nov-2024(ie, Nov 11 to Nov 17).

[ErikG]

Hi @Asfak

week definition you can set in the layout settings

week representation i guess you need to create a seperate field to show the ending date only.

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

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 ha…

Drill down you can achieve in the visual, visual menu or via control.

BR

[Asfak]

Thanks for your response. I’m able to display the data with the week ending date. However, the drill-down feature doesn’t work with it when using a derived data field as mentioned in the other post.

Please see below details.

1. Screenshot showing the Start of Week set to Monday, i.e., the End of Week is Sunday.
   
   

The output is exactly i want(17 Nov) but the aggregation is set to Day in order to achieve this.

Hence, the drill down to day option is not available in this case.

2. If I change the aggregation to “week,” the report will display the week start date(11 Nov). Moreover, with the custom WeekEnd field, the drill-down feature doesn’t work as well.
   
   

If do drill down to Day it will just show Nov 17th.

3. Additionally, if I use the actual date column BudgetedDate and set it to “week,” it allows drilling down to the day level of that week. However, the week is displayed with the start date(11 Nov) rather than the week end date(17 Nov).

In conclusion, what I need is the ability to drill down to the day level of that week while displaying the week ending date instead of the week starting date.

Appreciate your guidance on this.

[ErikG]

HI @Asfak

1. Last screenshot indicates you are already on day level.
2. Is it possible that you only have data on 17th and that is the reason why to only see 17th Nov after drill down in week 11th Nov?
3. What do you mean by “allows drilling down to the day level of that week”? and what is wrong with “the week is displayed with the start date(11 Nov) rather than the week end date(17 Nov).”?
   BR

[Asfak]

Yes, you are right. Day level drill down is working when using the week start date in the report.

However, the requirement from the user is to display the week-ending date and enable drill-down to the day level, which is not happening at the moment.

Current:
Display Week Start date=11/11/2024
Drill down to Day=11///2024 to 17/11/2024

Expected:
Display Week End date=17/11/2024
Drill down to Day=11///2024 to 17/11/2024

[Brett]

Hi @Asfak,
It’s been awhile since last communication on this thread. Did you have any remaining questions or were you able to find a work around for your case?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

[Brett]

Hi @Asfak,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!