Help creating charts by Month with Yearly average values also

[Ratier]

I have a table(in a Excel spreadsheet) with data resembling this:

Month	Quarter	Semester	Data
Jan	Q1	S1	1
Feb	Q1	S1	2
Mar	Q1	S1	3
Apr	Q2	S1	4
May	Q2	S1	5
Jun	Q2	S1	6
Jul	Q3	S2	7
Aug	Q3	S2	8
Sep	Q3	S2	9
Oct	Q4	S2	10
Nov	Q4	S2	11
Dec	Q4	S2	12

And i'd like to make a chart in QlikSebse that goes like those below:

If I use the Month, Quarter or the Semester Field as Dimension, I would no get the average value in the end. I also cannot create in my spreadsheet a Row for the average value because the first month of the next year goed right below the last month of the present year.

In each chart, the last column is the anual average value and in the Semester and Quarter chart, I would like to have the average value for each one also.

I had a similar problem but with accumulated values and I think that the solution may be similar to this one but I could not find a way to do it

Solved: Help creating charts by Month with Yearly accumula... - Qlik Community - 2107270

How can I create this charts without changing the spreadsheet structure?

[Or]

Have a look at the third example on this page:

https://community.qlik.com/t5/Design/Chart-Level-Scripting-Use-Cases-Samples-and-Examples/ba-p/19810...

One of the rare use cases for chart-level scripting...

[zfonline7888]

Load *
, Date(MonthStart(Date),'YYYY-MMM') as YrMth
, 'Q' & CEIL(NUM(MONTH(Date))/3) as YrQtr
;
Load *
Inline
[
Date, Value, Semester
2023-01-01, 1, S1
2023-02-01, 2, S1
2023-03-01, 3, S1
2023-04-01, 4, S1
2023-05-01, 5, S1
2023-06-01, 6, S1
2023-07-01, 7, S2
2023-08-01, 8, S2
2023-09-01, 9, S2
2023-10-01, 10, S2
2023-11-01, 11, S2
2023-12-01, 12, S2
]
;

Use avg. value reference line

[Ratier · Accepted solution]

I did something and it worked. Now I have both lines added, the sum and avg. 

SOMA:
LOAD * Inline [
Month, Quarter, Semester, Data
Jan, Q1, S1, 1
Fev, Q1, S1, 2
Mar, Q1, S1, 3
Apr, Q2, S1, 4
May, Q2, S1, 5
Jun, Q2, S1, 6
Jul, Q3, S2, 7
Aug, Q3, S2, 8
Sep, Q3, S2, 9
Oct, Q4, S2, 10
Nov, Q4, S2, 11
Dec, Q4, S2, 12

];

Temp:
LOAD
'Ac.' as Month,
'Ac.' as Quarter,
'Ac.' as Semester,
Sum(Data) as Data
Resident SOMA;

MÉDIA:
NoConcatenate LOAD * Inline [
Month, Quarter, Semester, Data
Jan, Q1, S1, 1
Fev, Q1, S1, 2
Mar, Q1, S1, 3
Apr, Q2, S1, 4
May, Q2, S1, 5
Jun, Q2, S1, 6
Jul, Q3, S2, 7
Aug, Q3, S2, 8
Sep, Q3, S2, 9
Oct, Q4, S2, 10
Nov, Q4, S2, 11
Dec, Q4, S2, 12

];

Temp2:
NoConcatenate LOAD
'Avg.' as Month,
'Avg.' as Quarter,
'Avg.' as Semester,
Avg(Data) as Data
Resident MÉDIA;