Expert Graph needed

[LebD]

I want to create the following graph, and I am not succeeding at it yet, so help is needed from an expert charter:

I have a complicated set of data with different indicators but 2 variables I am interested in. So I will create a graph for each.
I will simplify it below:

first column is Names. Second column is Start date (format 01.01.2021). Third Column is End Date. Fourth column is 1st variable.
I want to chart a period axis spreading let's say over 2 years. and the Y axis representing the variable of the 4th column.

The start date is important because it is represented on the X axis.

When a Name has a start date, it should trigger a vertical bar on the graph above the X axis corresponding to the amount in 4th column.
When a name has an End Date, it should trigger a vertical bar on the graph below the X axis on the negative side corresponding to the amount in 4th column too.

chart I think will get messy with all the data overlapping each other. So it should show Top 10 for each tick of the X axis over all the date period I selected like from 1.1.2021 till 31.12.2022

Advice appreciated

 

[ravi2628]

Names will be based on your selection or need to be plotted automatically.



I have Designed a Sample Graph I think this can help you sharing with sample snapshots.

Book5
	A	B	C	D	E	F	G	H
1	Names	Start Date	End date	Variable
2	Ravi	01.01.2021		10			Ravi
3	Suresh		03.01.2021	40
4							22.11.2020
5							01.01.2021	10
6							10.02.2021
Sheet1

Cell Formulas
Range	Formula
H5	H5	=IF(VLOOKUP(G2,A1:B3,2,0)=" ",VLOOKUP(G2,A1:D3,4,0)*-1,VLOOKUP(G2,A1:D3,4,0))
G4	G4	=G5-40
G5	G5	=IF(VLOOKUP(G2,A1:D3,2,0)=" ",VLOOKUP(G2,A1:D3,3,0),VLOOKUP(G2,A1:D3,2,0))
G6	G6	=G5+40

Cells with Data Validation
Cell	Allow	Criteria
G2	List	Ravi,Suresh

 

[LebD]

Thank you but names are plotted automatically. From the same list you selected as source data.
So I decided to go down the pivot table road. but have troubles selecting Top 10 per month.

 

[LebD]

I changed the Stacked 100% chart to Stacked (value) and added a filter to show values greater than x. So getting closer to the target