Dynamic date filter on showing values in past, present and future

[Mike22]

Dear Friends,

I am writing to you looking for advice. I want to display a map showing who is where in the various office locations we have.

I have the following tables:

Staff table
Staff ID* | Name | Working location | Home location

Travel table
Staff ID | Trip Number*| Start date| End Date

Travel location
Trip Number* | Destination City

Leave Table
Staff ID* | Leave Type | Start Date | End Date

All fields with * are unique in the table and form the relationships with the other tables.

The challenge I have is that in the report with the map I want to have a filter which is relative. Allowing me to select any date in the past or future and showing on the map where the staff is.

- If a staff is on travel the map should display the staff or multiple staff in the destination city categorized as "travel"

-If staff is on leave it should show them at the working location but categorized as "on leave"

-If staff is on leave and the leave type is "home" it should show the staff at the home location categorized as "Home"

-If the staff is not on leave and not traveling it should show at the working location catecorized as "on duty"

-The bubble size on the map is determined by the distinct count of staff id in the location. Categorization is important as there might be staff with different categories at the same location.

Thank you in advance for the usual support.
Mike

[v-yuta-msft]

@Mike22 ,

---

@Mike22 wrote:

Dear Friends,

I am writing to you looking for advice. I want to display a map showing who is where in the various office locations we have.

I have the following tables:

Staff table
Staff ID* | Name | Working location | Home location

Travel table
Staff ID | Trip Number*| Start date| End Date

Travel location
Trip Number* | Destination City

Leave Table
Staff ID* | Leave Type | Start Date | End Date

All fields with * are unique in the table and form the relationships with the other tables.

The challenge I have is that in the report with the map I want to have a filter which is relative. Allowing me to select any date in the past or future and showing on the map where the staff is.

- If a staff is on travel the map should display the staff or multiple staff in the destination city categorized as "travel"

-If staff is on leave it should show them at the working location but categorized as "on leave"

-If staff is on leave and the leave type is "home" it should show the staff at the home location categorized as "Home"

-If the staff is not on leave and not traveling it should show at the working location catecorized as "on duty"

-The bubble size on the map is determined by the distinct count of staff id in the location. Categorization is important as there might be staff with different categories at the same location.

Thank you in advance for the usual support.
Mike

---

Could you share some sample data and show the expected result?

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mike22]

Hello @v-yuta-msft 

Below is the sample data I saw that Esri Map can handle the multiple color bubbles very well.

Staff Table
Staff ID	Name	Working Location	Home Location
123456	John Smith	Paris	Berlin
654321	Anne Mata	London	Madrid
52875	Mark Green	Chicago	Paris
565578	Veronica Eli	Paris	Lisbon
5558	Dennis Red	Paris	Athens

Travel Table
Staff Id	Trip Number	Start Date	End Date
123456	5874	12/04/2019	15/04/2019
654321	6542	05/05/2019	10/05/2019

Travel Location
Trip Number	Destination City
5874	London
6542	Oslo

Leave Table
Staff Id	Leave Type	Start Date	End Date
52875	Home	01/05/2019	20/05/2019
565578	Normal	05/05/2019	10/05/2019

When setting the master date filter on the dashboard to be 6th May 2019 the maps should show the following:

-Paris should have a bubble with count 2 in green color showing that John Smith and Dennis Red are on duty in Paris

-Paris should have another bubble with count 1 in grey showing that Mark Green is on leave in his home city

-Paris should have another bubble of count 1 in red showing that Veronica Eli is on normal leave and location is unknown.

-Oslo should have a bubble with count 1 in blue color showing that Anne Mata is in Oslo on travel

If we set the mater date filter to 13th of April everyone will show up in green color at there working location except John Smith who will show up in blue color with a cout 1 bubble in London. 

Hope this clarifies. Thanks!

[Mike22]

Hello, 

Just wondering if anyone has any idea on how to implement this. 

Thanks!

[Mike22]

@v-yuta-msft 

Just wondering if you have any idea on how to move forward on this and if it is feasable.

Thanks,

Mike

[Mike22]

Hello!

Just back to see if anyone has suggestions.

Thanks.