How to find no of associates visited once, twice thrice, 4 days and 5 days 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

[sidhiq91]

@Satyajeetkhatke  Please see below the code that I have used in the Script Editor. It is bit lengthy, be careful while you are trying to replicate the same.

NoConcatenate
Temp:
LOAD
emp_id,
visited
FROM [lib://Qlik Community Practice/WFO sample data.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate
Temp1:
Load WeekNumber,
[Number of times visited],
Total_employee_Count;

Load Count(emp_id) as Total_employee_Count,
Count_Employess,
WeekNumber,
[Number of times visited]
Group by Count_Employess,
WeekNumber,
[Number of times visited];

Load *,
If(Count_Employess=1,'Visited once',
if(Count_Employess=2,'Visited twice',
if(Count_Employess=3,'Visited thrice',
if(Count_Employess=4,'Visited 4 times','Visited 5 times')))) as [Number of times visited];

Load count(emp_id) as Count_Employess,
emp_id,
WeekNumber
group by WeekNumber,emp_id;

Load emp_id,
visited,
if(month(weekend(visited))= month(visited),
div(day(WeekEnd(visited)),7),div(day(Weekend(visited,-1)),7)+1)+1 as WeekNumber
Resident Temp;

NoConcatenate
Temp2:
Load WeekNumber,

Total_employee_Count as [Visited Once]
Resident Temp1
where [Number of times visited]='Visited once';

left join (Temp2)
Load WeekNumber,

Total_employee_Count as [Visited twice]
Resident Temp1
where [Number of times visited]='Visited twice';

left join (Temp2)
Load WeekNumber,

Total_employee_Count as [Visited thrice]
Resident Temp1
where [Number of times visited]='Visited thrice';

left join (Temp2)
Load WeekNumber,

Total_employee_Count as [Visited 4 times]
Resident Temp1
where [Number of times visited]='Visited 4 times';

left join (Temp2)
Load WeekNumber,

Total_employee_Count as [Visited 5 times]
Resident Temp1
where [Number of times visited]='Visited 5 times';

Drop table Temp,Temp1;

Exit Script;

If this resolves your issue, please like and accept it as a solution.