topic Re: Count last week values, based on filter in QlikView

Count last week values, based on filter

Vitalii — Tue, 14 Nov 2023 11:34:08 GMT

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 — Tue, 14 Nov 2023 11:45:37 GMT

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