Attendance for pickup & drop

[avicric]

Hi All,

I need help with this....here where I am stuck...

Goal : attendance count

data I have is for pickup & drop service...so a person may do pickup & drop or only pickup or only drop....

is there a way to get the attendance for about 300+ people...

Thanks for taking the time to help....

 

[Logit]

Do you have a workbook already created with the data ? If so, post it on a website like DropBox.com or similar so it can be reviewed.

If you don't have a workbook already you'll need to describe in clearer detail what it is you envision.

From you initial description it sounds like you are wanting something like :

How do you envision working with the above if this is what you are speaking of ?

 

[avicric]

Do you have a workbook already created with the data ? If so, post it on a website like DropBox.com or similar so it can be reviewed.

If you don't have a workbook already you'll need to describe in clearer detail what it is you envision.

From you initial description it sounds like you are wanting something like :

View attachment 91982


How do you envision working with the above if this is what you are speaking of ?

No Sir... what you posted that information I know to get
my bad should have posted an image at least

here's what I need.... below is a sample data I need to get the attendance for each person for each date...as you there are two columns I need to refer for name and two columns to refer for date....

pickup	drop	pickup date	drop date
Kalpesh	Kalpesh	19-05-23	20-05-23
Nadir	Shan	19-05-23	20-05-23
Ali	Hussain	19-05-23	20-05-23
Shan	Talib	19-05-23	20-05-23
Hussain	gautam	19-05-23	20-05-23
Talib	Nadir	19-05-23	20-05-23
gautam	Ali	19-05-23	20-05-23
Nadir	Kalpesh	19-05-23	20-05-23
Ali	Nadir	19-05-23	20-05-23
Shan	Ali	19-05-23	20-05-23
Talib	Ali	20-05-23	20-05-23
Ali	Ali	20-05-23	20-05-23
Kalpesh	Kalpesh	20-05-23	20-05-23
Ali	Nadir	20-05-23	20-05-23
Shan	Ali	20-05-23	20-05-23
Hussain	Shan	20-05-23	20-05-23
Talib	Hussain	20-05-23	20-05-23
gautam	gautam	20-05-23	20-05-23
Talib	Ali	20-05-23	20-05-23
Ali	Ali	20-05-23	20-05-23
Kalpesh	Kalpesh	20-05-23	20-05-23

 

[Logit]

 

[avicric]

View attachment 91985

a little more from my side.. (just want to know if the person worked as per the system)

Name	20-05-23	19-05-23	18-05-23	17-05-23	16-05-23	15-05-23	14-05-23
Kalpesh	1	1	1	1	1	1	1
Nadir	1	1	1	1	1	1	1
Ali	1	1	1	1	0	1	1
Shan	1	1	1	1	1	0	1
Hussain	1	0	1	1	1	1	1
Talib	1	1	1	1	1	1	1
gautam	1	0	1	1	0	1	0

 

[Logit]

I believe what you are seeking is a means to 'transpose the data' from existing layout to multi-column layout. Regretfully someone else will need to assist with that
formula.

Best wishes.

 

[avicric]

I believe what you are seeking is a means to 'transpose the data' from existing layout to multi-column layout. Regretfully someone else will need to assist with that
formula.

Best wishes.

Thank you for trying

 

[GraH]

Maybe try this Power Query Solution at "UI/beginners level"

Given this data:

Book1
	D	E	F	G
2	pickup	drop	pickup date	drop date
3	Kalpesh	Kalpesh	19-05 23	20-05 23
4	Nadir	Shan	19-05 23	20-05 23
5	Ali	Hussain	19-05 23	20-05 23
6	Shan	Talib	19-05 23	20-05 23
7	Hussain	gautam	19-05 23	20-05 23
8	Talib	Nadir	19-05 23	20-05 23
9	gautam	Ali	19-05 23	20-05 23
10	Nadir	Kalpesh	19-05 23	20-05 23
11	Ali	Nadir	19-05 23	20-05 23
12	Shan	Ali	19-05 23	20-05 23
13	Talib	Ali	20-05 23	20-05 23
14	Ali	Ali	20-05 23	20-05 23
15	Kalpesh	Kalpesh	20-05 23	20-05 23
16	Ali	Nadir	20-05 23	20-05 23
17	Shan	Ali	20-05 23	20-05 23
18	Hussain	Shan	20-05 23	20-05 23
19	Talib	Hussain	20-05 23	20-05 23
20	gautam	gautam	20-05 23	20-05 23
21	Talib	Ali	20-05 23	20-05 23
22	Ali	Ali	20-05 23	20-05 23
23	Kalpesh	Kalpesh	20-05 23	20-05 23
Sheet1

With this PQ :

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"pickup", type text}, {"drop", type text}, {"pickup date", type date}, {"drop date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Index", {{"pickup date", type text}}, "nl-BE"),{"pickup", "pickup date"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Pickup"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"drop date", type text}}, "nl-BE"),{"drop", "drop date"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Drop"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {"Index"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByEachDelimiter({"|"}, QuoteStyle.Csv, false), {"Name", "Date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name", type text}, {"Date", type date}})
in
    #"Changed Type1"

This pivot is possible:

Count of Index	Column Labels
Row Labels	Drop	Pickup	Grand Total
Ali	7	5	12
19/05/2023		2	2
20/05/2023	7	3	10
gautam	2	2	4
19/05/2023		1	1
20/05/2023	2	1	3
Hussain	2	2	4
19/05/2023		1	1
20/05/2023	2	1	3
Kalpesh	4	3	7
19/05/2023		1	1
20/05/2023	4	2	6
Nadir	3	2	5
19/05/2023		2	2
20/05/2023	3		3
Shan	2	3	5
19/05/2023		2	2
20/05/2023	2	1	3
Talib	1	4	5
19/05/2023		1	1
20/05/2023	1	3	4
Grand Total	21	21	42

 

[avicric]

Maybe try this Power Query Solution at "UI/beginners level"

Given this data:

Book1
	D	E	F	G
2	pickup	drop	pickup date	drop date
3	Kalpesh	Kalpesh	19-05 23	20-05 23
4	Nadir	Shan	19-05 23	20-05 23
5	Ali	Hussain	19-05 23	20-05 23
6	Shan	Talib	19-05 23	20-05 23
7	Hussain	gautam	19-05 23	20-05 23
8	Talib	Nadir	19-05 23	20-05 23
9	gautam	Ali	19-05 23	20-05 23
10	Nadir	Kalpesh	19-05 23	20-05 23
11	Ali	Nadir	19-05 23	20-05 23
12	Shan	Ali	19-05 23	20-05 23
13	Talib	Ali	20-05 23	20-05 23
14	Ali	Ali	20-05 23	20-05 23
15	Kalpesh	Kalpesh	20-05 23	20-05 23
16	Ali	Nadir	20-05 23	20-05 23
17	Shan	Ali	20-05 23	20-05 23
18	Hussain	Shan	20-05 23	20-05 23
19	Talib	Hussain	20-05 23	20-05 23
20	gautam	gautam	20-05 23	20-05 23
21	Talib	Ali	20-05 23	20-05 23
22	Ali	Ali	20-05 23	20-05 23
23	Kalpesh	Kalpesh	20-05 23	20-05 23
Sheet1

With this PQ :

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"pickup", type text}, {"drop", type text}, {"pickup date", type date}, {"drop date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Index", {{"pickup date", type text}}, "nl-BE"),{"pickup", "pickup date"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Pickup"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"drop date", type text}}, "nl-BE"),{"drop", "drop date"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Drop"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {"Index"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByEachDelimiter({"|"}, QuoteStyle.Csv, false), {"Name", "Date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name", type text}, {"Date", type date}})
in
    #"Changed Type1"

This pivot is possible:

Count of Index	Column Labels
Row Labels	Drop	Pickup	Grand Total
Ali	7	5	12
19/05/2023		2	2
20/05/2023	7	3	10
gautam	2	2	4
19/05/2023		1	1
20/05/2023	2	1	3
Hussain	2	2	4
19/05/2023		1	1
20/05/2023	2	1	3
Kalpesh	4	3	7
19/05/2023		1	1
20/05/2023	4	2	6
Nadir	3	2	5
19/05/2023		2	2
20/05/2023	3		3
Shan	2	3	5
19/05/2023		2	2
20/05/2023	2	1	3
Talib	1	4	5
19/05/2023		1	1
20/05/2023	1	3	4
Grand Total	21	21	42

Thanks Grah....
I am able to get the counts what I am looking to get is the information if a person is present/worked on a particular date....

my problem is Ali ( the rep/agent) name can be populated in pickup or drop or both the fields and similarly the date....

what I am trying to say that a person/agent can only do pickup or he may only do drop.........or both on any given day......
if I am able to get this data from the system will not have to rely on manual data....

Thanking you in advance...

 

[avicric]

Thanks Grah....
I am able to get the counts what I am looking to get is the information if a person is present/worked on a particular date....

my problem is Ali ( the rep/agent) name can be populated in pickup or drop or both the fields and similarly the date....

what I am trying to say that a person/agent can only do pickup or he may only do drop.........or both on any given day......
if I am able to get this data from the system will not have to rely on manual data....

Thanking you in advance...

Hi All,

Got something working....
You will need a calendar table also need to create relationship between the pick & drop date(active/inactive)
Measure:=
IF( CALCULATE( COUNTROWS('YourMainTable'), ALLEXCEPT('YourMainTable', 'YourMainTable'[pickup], 'YourMainTable'[drop]), FILTER( VALUES('YourCalendarTable'[Date]), CONTAINS('YourMainTable', 'YourMainTable'[pickupdate], 'YourCalendarTable'[Date]) && CONTAINS('YourMainTable', 'YourMainTable'[dropdate], 'YourCalendarTable'[Date]) ) ) > 0, 1, Blank())

Phew