in last 30days rolling, need to find out project which has been updated in last 7days from those each 30days date.

[shivang_purwar]

For a table in qlik sense
1. We have date column for last 30days(Rolling_date)
2. We have a date column(Last_updated_date) for which project has updated anything or not
Question :  to check for each date in last 30days rolling, need to find out project which has been updated in last 7days from those each 30days date.

currently we are doing       count(distinct if((today()-LAST_UPDATED_DATE)<= 7,PID)) for today's date. But for last 30days each date, we are stucked.

Request you all to please kindly help.

[edwin]

can you add sample data?

[shivang_purwar]

for each pid last updated date has been updated for that rollingdate, if from rolling date to within last 7 days projects has been update then we need to count that project for that date.

[edwin · Accepted solution]

does that mean for 11/09/2021 you count back 7 days so all PIDs where lastupdatedate >=11/07 and <=11/09?

i would solve this by linking the two tables together such when a date is selected, all PIDs/rows in the other table is selectable thus the UI will run faster:

dates:
load date(today()-iterno()+1) as Date
while iterno()<90;
load 1 AutoGenerate(1);


PIDs:
load rowno() as rowID, * inline [
update, PID
11/6/2021,1
11/3/2021, 2
10/30/2021, 3
9/30/2021,4
];


NoConcatenate
tmp:
load Date Resident dates;

outer join (tmp)
load rowID, update Resident PIDs;

NoConcatenate
Bridge:
load rowID, Date Resident tmp
where update>=Date-7 and update<=Date;

drop table tmp;  drop field update from Bridge;

[edwin]

thus in my example, when date = 11/7.  PIDs 1 and 2 will be selectable.  in your UI you just count DISTINCT PID

[shivang_purwar]

Thanks alot this  will also work.  I used a different approach. I am creating flag, which check based on today and subtracting it with last updated date and comparing it with 7.

[edwin]

not sure how you did your flag.  the issue with passive flags added to the table is that they may not be dependent on other tables which when you allow users to filter, the flag may not work anymore

however, use what you think is best