Count last week values, based on filter

[Vitalii]

Hi everybody!

I have a table

Id     date                week                                       week_num

1       31.10.2023   2023-10-30-2023-11-05   44

23     30.10.2023   2023-10-30-2023-11-05   44    

444      04.11.2023   2023-10-30-2023-11-05   44

67       23.10.2023   2023-10-23-2023-10-29   43

009       26.10.2023   2023-10-23-2023-10-29   43

i need to count ID based on filter of week 

i tried

COUNT( {<week_num={$(=GetFieldSelections(week_num)-1)}>} ID)

But it works when you select in filters week_num (not user friendly)

maybe you have some ideas. Thanks

[Aasir · Accepted solution]

First create a variable for prev week

vPrevWeek:
LET vCurrentWeek = GetCurrentField('week_num');
LET vPrevWeek = Num(WeekStart(WeekEnd(Date(Min(date)))) - 7, 'YYYY-MM-DD'));

$(vPrevWeek)

and use it in expression
COUNT({<week_num={$(vPrevWeek)}>} ID)

This expression will count the IDs based on the week prior to the minimum date in your data without relying on direct filter selections.

Make sure to check the date formats