How to find No of associates visited office in a week

[Satyajeetkhatke]

Hello Everyone,

I want to show how many times associates visited the office in a week like how many associates visited once in a week, twice a week, thrice a week, 4 days, and 5 days in week.

I have attached the sample data.

can anyone help with this?

Thanks in advance

[vinieme12]

As below

Main:
load emp_id
,Date#(visited,'YYYY-DD-MM') as visited
,Week(Date#(visited,'YYYY-DD-MM'),0,0,4) as IsoWeek
inline 
[
emp_id,visited
1,2022-04-07
2,2022-04-07
4,2022-04-07
5,2022-04-07
3,2022-04-07
2,2022-05-07
3,2022-05-07
5,2022-05-07
2,2022-06-07
1,2022-06-07
4,2022-06-07
5,2022-06-07
2,2022-07-07
6,2022-07-07
1,2022-07-07
7,2022-07-07
2,2022-08-07
3,2022-08-07
5,2022-08-07
5,2022-11-07
7,2022-11-07
2,2022-11-07
4,2022-11-07
5,2022-12-07
2,2022-12-07
7,2022-12-07
3,2022-12-07
7,2022-13-07
5,2022-13-07
1,2022-13-07
5,2022-14-07
];

Row Dimension

=IsoWeek

Column Dimension

=aggr(if(count(emp_id)=1,'Once',
if(count(emp_id)=2,'Twice',
if(count(emp_id)=3,'Thrice',
if(count(emp_id)=4,'Four Times',
if(count(emp_id)=5,'Five Times',
if(count(emp_id)=6,'6 Times',
if(count(emp_id)=7,'Everyday'))))))),IsoWeek,emp_id)

Measure

count(Distinct emp_id)