Gantt Chart SVG

[Kabulino]

Flat Gantt Chart

The basic idea of how it works: It’s an SVG that lives in a parent table and graphically represents the duration of the related child records in a timeline, according to their start and end date for the selected year. It’s possible to view other years using actions.

Child Table (Events):

[ID_Proyect] is a Ref of the Projects table.

Parent Table (Proyects):

How to Implement the Functionality:

1.- First, we will create a Slice of our child table (Events), which I will call Events_Year_Selected, to filter and display the records corresponding to the year we want to view. Slice Formula:

OR(YEAR([Start_Date])=YEAR(TODAY())+[ID_Proyect].[Year_Counter],
YEAR([Final_Date])=YEAR(TODAY())+[ID_Proyect].[Year_Counter])

2.- Create the virtual column for the parent table (Proyects)

[Related Events_Year_Selected]

REF_ROWS("Events_Year_Selected", "ID_Proyect")

3.- Create the VC for our child table (Events)

[X_SVG]

IFS(
AND(YEAR([Start_Date])=YEAR(TODAY())-1+[ID_Proyect].[Year_Counter],
YEAR([Final_Date])=YEAR(TODAY())+[ID_Proyect].[Year_Counter]
),118,
YEAR([Start_Date])=YEAR(TODAY())+[ID_Proyect].[Year_Counter],75+MONTH([Start_Date])*43+DAY([Start_Date])*1.413)

[Y_SVG]

-10+count(
split(
left(
concatenate([ID_Proyect].[Related Events_Year_Selected]),
find(
[_THISROW],
concatenate([ID_Proyect].[Related Events_Year_Selected])
) + (LEN([_THISROW]) - 1)
),
" , ")
)*35

[Width_SVG]

IFS(
AND( 
YEAR([Start_Date])=YEAR(TODAY())-1+[ID_Proyect].[Year_Counter],
YEAR([Final_Date])=YEAR(TODAY())+[ID_Proyect].[Year_Counter]
),
NUMBER(
SWITCH(MONTH([Final_Date]),
1,0,
2,43,
3,86,
4,129,
5,172,
6,215,
7,258,
8,301,
9,344,
10,387,
11,430,
12,473,
""))+DAY([Final_Date])*1.413,

YEAR([Start_Date])=YEAR(TODAY())+[ID_Proyect].[Year_Counter],
(HOUR([Final_Date]-[Start_Date])/24)*1.413
)

[Bit_Gantt_SVG]

CONCATENATE(
"<text x='0' y=""",[Y_SVG]+20, """ font-family='Arial' font-size='14' fill='black' font-weight='bold'>",LEFT([Name],15),"</text>
<line x1='118' y1=""",[Y_SVG]+35, """ x2='634' y2=""",[Y_SVG]+35,""" style='stroke:rgba(128, 128, 128, 0.5);stroke-width:1;stroke:gray' />

<!-- Rectángulo -->
<rect x=""",[X_SVG],""" y=""",[Y_SVG],""" width=""",[Width_SVG],""" height='35' rx='10' ry='10' fill=""",[Color],""">
</rect>"
)

4.- Create the remaining virtual columns for the parent table (Proyects)

[Y_SVG]

40+COUNT([Related Events_Year_Selected])*35

[Gantt_SVG]

"data&colon;image/svg+xml;utf8,"&CONCATENATE("<svg xmlns='http://www.w3.org/2000/svg' width='640' height=""",[Y_SVG],"""> 
<!-- Eje X (Tiempo en meses) con líneas verticales -->

<line x1='118' y1=""",[Y_SVG],""" x2='118' y2='25' style='stroke:rgba(128, 128, 128, 0.5);stroke-width:2' /> 
<line x1='161' y1=""",[Y_SVG],""" x2='161' y2='25' style='stroke:rgba(128, 128, 128, 0.5);stroke-width:2' /> 
<line x1='204' y1=""",[Y_SVG],""" x2='204' y2='25' style='stroke:rgba(128, 128, 128, 0.5);stroke-width:2' /> 
<line x1='247' y1=""",[Y_SVG],""" x2='247' y2='25' style='stroke:rgba(128, 128, 128, 0.5);stroke-width:2' /> 
<line x1='290' y1=""",[Y_SVG],""" x2='290' y2='25' style='stroke:rgba(128, 128, 128, 0.5);stroke-width:2' /> 
<line x1='333' y1=""",[Y_SVG],""" x2='333' y2='25' style='stroke:rgba(128, 128, 128, 0.5);stroke-width:2' /> 
<line x1='376' y1=""",[Y_SVG],""" x2='376' y2='25' style='stroke:rgba(128, 128, 128, 0.5);stroke-width:2' /> 
<line x1='419' y1=""",[Y_SVG],""" x2='419' y2='25' style='stroke:rgba(128, 128, 128, 0.5);stroke-width:2' /> 
<line x1='462' y1=""",[Y_SVG],""" x2='462' y2='25' style='stroke:rgba(128, 128, 128, 0.5);stroke-width:2' /> 
<line x1='505' y1=""",[Y_SVG],""" x2='505' y2='25' style='stroke:rgba(128, 128, 128, 0.5);stroke-width:2' /> 
<line x1='548' y1=""",[Y_SVG],""" x2='548' y2='25' style='stroke:rgba(128, 128, 128, 0.5);stroke-width:2' /> 
<line x1='591' y1=""",[Y_SVG],""" x2='591' y2='25' style='stroke:rgba(128, 128, 128, 0.5);stroke-width:2' /> 
<line x1='634' y1=""",[Y_SVG],""" x2='634' y2='25' style='stroke:rgba(128, 128, 128, 0.5);stroke-width:2' /> 
<line x1='118' y1='25' x2='634' y2='25' style='stroke:rgba(128, 128, 128, 0.5);stroke-width:1;stroke:gray' />
<!-- constante de x = +43 -->

<text x='128' y=""",[Y_SVG],""" font-family='Arial' font-size='14' fill='black' font-weight='bold'>Jan.</text> 
<text x='171' y=""",[Y_SVG],""" font-family='Arial' font-size='14' fill='black' font-weight='bold'>Feb.</text> 
<text x='214' y=""",[Y_SVG],""" font-family='Arial' font-size='14' fill='black' font-weight='bold'>Mar.</text> 
<text x='257' y=""",[Y_SVG],""" font-family='Arial' font-size='14' fill='black' font-weight='bold'>Apr.</text> 
<text x='300' y=""",[Y_SVG],""" font-family='Arial' font-size='14' fill='black' font-weight='bold'>May</text> 
<text x='338' y=""",[Y_SVG],""" font-family='Arial' font-size='14' fill='black' font-weight='bold'>June</text> 
<text x='383' y=""",[Y_SVG],""" font-family='Arial' font-size='14' fill='black' font-weight='bold'>July</text> 
<text x='425' y=""",[Y_SVG],""" font-family='Arial' font-size='14' fill='black' font-weight='bold'>Aug.</text> 
<text x='466' y=""",[Y_SVG],""" font-family='Arial' font-size='14' fill='black' font-weight='bold'>Sep.</text> 
<text x='515' y=""",[Y_SVG],""" font-family='Arial' font-size='14' fill='black' font-weight='bold'>Oct.</text> 
<text x='558' y=""",[Y_SVG],""" font-family='Arial' font-size='14' fill='black' font-weight='bold'>Nov.</text> 
<text x='601' y=""",[Y_SVG],""" font-family='Arial' font-size='14' fill='black' font-weight='bold'>Dec.</text>
",
SUBSTITUTE([Related Events_Year_Selected][Bit_Gantt_SVG]," , ","
"),

IF(YEAR(TODAY())+[Year_Counter]=YEAR(TODAY()),CONCATENATE("

<!-- Línea de tiempo hoy --> 
<line x1=""",NUMBER(SWITCH(MONTH(TODAY()), 
1,118, 
2,161, 
3,204, 
4,247, 
5,290, 
6,333, 
7,376, 
8,419, 
9,462, 
10,505, 
11,548,
12,591, 
""))+DAY(TODAY())*1.413,""" y1='12' x2=""",NUMBER(SWITCH(MONTH(TODAY()), 
1,118, 
2,161, 
3,204, 
4,247, 
5,290, 
6,333, 
7,376, 
8,419, 
9,462, 
10,505, 
11,548,
12,591, 
""))+DAY(TODAY())*1.413,""" y2=""",[Y_SVG]-15,""" style='stroke:dashed;stroke-width:2;stroke:rgb(0, 0, 255, 0.65) ' />

<!-- Etiqueta de 'Hoy' --> 
<text x=""", NUMBER(SWITCH(MONTH(TODAY()), 
1,118, 
2,161, 
3,204, 
4,247, 
5,290, 
6,333, 
7,376, 
8,419, 
9,462, 
10,505, 
11,548,
12,591, ""))+DAY(TODAY())*1.413-50,""" y='12' font-family='Arial' font-size='14' fill=' rgb(0, 0, 255, 0.65) ' > ",TEXT(TODAY(),"D/MM/YY")," </text>"),""),"

<text x='0' y='20' font-family='Arial' font-size='20' fill='black' font-weight='bold'>",YEAR(TODAY())+[Year_Counter],"</text> 
</svg>" )

5.- Create the 3 actions that control the SVG to scroll through the years.

These should be positioned Inline attached to the [Gantt_SVG] column. All 3 actions do the same thing (Determine the value of [Year_Counter]):

action Last Year

[Year_Counter]-1 

action This Year

"" 

action Next Year

[Year_Counter]+1

(this is the order in which the actions should be displayed).

Disadvantages:

When changing the year, the SVG will deform for 3-5 seconds while synchronization occurs (when the sync happens in the background). Therefore, I recommend using a forced synchronization after each action or disabling Delayed Sync.

Comments:

It is possible to increase the number of available colors for events by creating an RGB color table.

Special thanks to:

@MultiTech , @Suvrutt_Gurjar , @Steve and many other great appsheet developers who, through their posts and videos, helped me understand the concepts necessary to make this Gantt SVG

[Suvrutt_Gurjar]

Hi @Kabulino ,

Thank you very much for the useful tip. Will definitely sytudy more.

Would you please look into making the sample app you shared public. When I tried to access I could not do so.

[Kabulino]

Sorry haha ​​this is the second time I try to share an application, it seems to me that it is already a public sample, please try again

[Suvrutt_Gurjar]

Thank you very much @Kabulino .

All good now. I can see the app now. I must say that at first glance the Gantt chart looks impressive. Great job.

I may request you to include a screenshot of the Gantt chart in the tip post so that the readers get a good idea of your great wirk even before opening the app.

[Arni_Kli]

Great job @Kabulino ! Thank you! Could you suggest a solution how to order events by start date in this chart?

[Kabulino]

Yes @Arni_Kli , the first idea that comes to mind is to modify the VC [Y_SVG] of the Events table..
The new formula would be

-10+count(
split(
left(
concatenate(**ORDERBY(**[ID_Proyect].[Related Events_Year_Selected]**,[Start_Date])**),
find(
[_THISROW],
concatenate(**ORDERBY(**[ID_Proyect].[Related Events_Year_Selected]**,[Start_Date]))**
) + (LEN([_THISROW]) - 1)
),
" , ")
)*35

Tell me how it works for you

[Arni_Kli]

@Kabulino , it works! Thanks a lot!

[Arni_Kli]

@Kabulino , could you comment some digits of formula?
[X_SVG]

IFS(
AND(YEAR([Start_Date])=YEAR(TODAY())-1+[ID_Proyect].[Year_Counter],
YEAR([Final_Date])=YEAR(TODAY())+[ID_Proyect].[Year_Counter]
),118,
YEAR([Start_Date])=YEAR(TODAY())+[ID_Proyect].[Year_Counter],75+MONTH([Start_Date])*43+DAY([Start_Date])*1.413)

75 - is it left margin of chart for events names?
43 - is it a distance between vertical lines?
1.413 - what is it and what is it for? This number also appears in [Width_SVG] and [Gantt_SVG].

Thoughts : it would be interesting to make such chart with dynamic resolution at 100% of the available width of screen.

[Kabulino]

Yes, you are correct.
75 is the margin of the chart
43 is the distance between month and month (distance between vertical lines).
To determine the width that a day would take 43*12/365=1.413

I find the idea of ​​a dynamic resolution of 100% of the available screen width interesting, although I think it will require a lot of trial and error.

[Arni_Kli]

@Kabulino , thanks. If you will develop this idea in future please share your approach.

[Denzil_Snyman]

• @Kabulino Thank you for your great work and sharing it here with us, would you know more or less how many Y Axis entries we can display before it cuts off due to the image size limits, if there is a limit perhaps there’s a way to dynamically allow bars to go slightly thinner but obviously up to a point so that it’s still legible.*

Again thank you for sharing, SVG building into appsheet is time consuming to get it right and usable like this…

I noticed if I add more than 6 items the width get less for all the items, perhaps we can figure a way to allow the view box to auto adjust however I know nothing about SVG attributes…

[Kabulino]

As it is, it can load 1000 entries and I don’t know the limit (the more entries it has, the longer the sync time will be), effectively the SVG adjusts its height depending on how many entries it has and Appsheet shrinks it when trying to show the full image, the more entries the smaller the image will appear. The SVG shows 10 perfectly readable entries and from then on it will be necessary to click on the image to see it with Appsheet’s zoom.

And speaking of limitations, I’m seeing that the SVG breaks if we use < or > in the event name.

With the + symbol the name is not displayed (this seems more like a problem with sheets since it recognizes it as a formula)

[NicolasB]

hello
Nice work ! I like it a lot.

Another case where SVG doesn’t work : when the name contains the ‘&’ character.

[Denzil_Snyman]

@NicolasB It can be done with some clever Encoding.

Substitute “&“ with “%26“ on final SVG code and also ““““ with “%22“.

“”“” is double quotes… I. E name column having string (Hello “Ola”)

[NicolasB]

thank you very much

I used substitute but I didn’t know I could use hex codes.