Help creating charts by Month with Yearly accumulated 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 accumulated value in the end. I also cannot create in my spreadsheet a Row for the accumulated value because the first month of the next year goed right below the last month of the present year.

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

[therealdees · Accepted solution]

Hi!

I'm not sure if it's possible via chart expressions, but I'm afraid you'll have to use the Loader to achieve this. You don't have to manipulate the Excel sheet data directly, but you could concatenate a new value for the fields doing something like this:

Temp:
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:
LOAD
'AC.' as Month,
'AC.' as Quarter,
'AC.' as Semester,
Sum(Data) as Data
Resident Temp;

Note that "Temp" would be loaded as you normally do. Just Sum the values in a second table and add the name for the dimensions as you wish (in this case "AC." would be the same throughout every dimension of the table, while "data" is the value you're summing and using as a measure in the chart.

[Ratier]

Thank you! Works just fine!

Tested the code first as you wrote. Next step is trying to aply the "Temp2:" part to the Loaded excel table

[therealdees · Accepted solution]

You can add it simply by matching the field names. Qlik will understand it's the same data structure and concatenate the values. For example, let's say your data comes directly from a folder:

ExcelPeriodData:

LOAD

*
FROM [lib://{the folder path}/{the file name}.xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate(ExcelPeriodData) // You could be explicit and use the Concatenate command

LOAD

LOAD
'AC.' as Month,
'AC.' as Quarter,
'AC.' as Semester,
Sum(Data) as Data

Resident ExcelPeriodData;