Getting only previous last two weeks from the selected end date

Question & Answer
,
1

Hi Friends,

I am using a calculated field to generate data for weeks. The weeks are created based on the selected end Date.
e.g if a user selects date 2024-06-28 the two weeks get created are as follows-
2024-06-29 to 2024-07-05, 2024-06-22 to 2024-06-28, 2024-06-15 to 2024-06-21

but I want only previous last two weeks to be generated only, not the whole series of weeks. I tried to use filters and choose last 2 weeks data as relative week filter but that works only for current weeks date, if I go back to older dates, it does not show data.

how can I restrict the data to be only two weeks always whatever date is selected.
e.g if I select 28-June i get the data for week 22-28 June and Previous week which is 15-21 June.

similarly when I choose 14 june as select date, the week should be 8-14 june and 1-7 june

my calculated field is here- which generates the start of the week
addDateTime(extract(‘WD’, ${SelectEndDate}), ‘DD’, truncDate(‘WK’, {line_item_usage_date}))
working ok-

Screenshot 2024-06-28 at 5.18.40 PM
Screenshot 2024-06-28 at 5.18.40 PM1860×780 102 KB

not working- I would expect it to show 1-7 june and 8-14 june

Screenshot 2024-06-28 at 5.19.14 PM
Screenshot 2024-06-28 at 5.19.14 PM1890×902 109 KB

2

Hi @Amit_Raj

Wondering is the requirement to have the start week as Friday ? instead of having default it as Sunday .

If yes QuickSight supports changing the Week Start day under the Analysis Layout Settings .

Please find the additional details here .

image

Thanks
VInod

3

Hi Vinod,
Thank you for replying. No start of the week should not be Friday rather dynamically change.
I understand the setting in the QuickSight to change start of week. But in my case, I would like to dynamically change start of the week, hence the custom field to generate start of the week dynamically.

e.g if the user selects 28 june friday- the current week that should be generated is -
22- 28 June. (sun- fri)
and previous 15- 21 June(Sat- Fri)
but when user select 18th June(wed) the current week that should be generate is -
12-18 June(wed-Tue)
and previous as 05-11 June(wed- Tue)

so basically the start of the week is not depending on the QuickSight setting of Sunday.

This custom field generates the start of the week date for me-

addDateTime(extract('WD', ${SelectEndDate}), 'DD', truncDate('WK', {line_item_usage_date}))

I have another custom field to generate end of the week-

addDateTime(6, 'DD', {cal_week_start})

now I concat both the cusotm field to get the view - start-of-week - end-of-week

concat(
  formatDate(truncDate('DD', {cal_week_start}), 'dd-MMM-yyyy'), 
  ' - ',
  formatDate(truncDate('DD', {cal_week_end}), 'dd-MMM-yyyy')
)

so basically I just want to compare two weeks(14days) that ends on the user selected date.

When user selects 28th june, 28th june becomes the current week’s end date.
(current week - 22-28 june)
(previous week -15-21 june.)

When user selects 18th june, 18th june becomes the current week’s end date.
(current week - 12-18 june)
(previous week -5-11 june.)

Thank you

4

Hi @Amit_Raj ,

Please refer to the resolution shared in the other thread with the same requirement. Let us know if the the query is resolved or this is for a different requirement.

Thanks,
Prantika

1 Like
5

Hi @Amit_Raj,
It’s been awhile since we last heard from you. Checking in to see if you had any additional questions or if the solutions provided above pointed you in the right direction.

If we do not hear back within 3 business days, I’ll go ahead and mark as solved.

Thank you!

6

Hi @Brett
Thank you for follow up. yes, this can be closed please.

Thank you

1 Like