Query relating to Line Chart

[#1]

Hi,

I need help to create a line chart as below:

I have an excel that provides details on Year_1, Year_2, Actual Sales, Actual Delivery and Actual Cumulative Delivery. I want to create a line graph with Year on 'X' Axis and the sum of figures for other columns plotted on Y axis.

Distinct values in Year_1 and Year_2 column are 2000, 2001, 2002, 2003

Actual Sales will be based on Year_1

Actual Delivery will be based on Year_2

Actual Cumulative Delivery will be cumulative of Actual Delivery figures for each year 'Year_2'.

I have created 3 expressions: Actual Sales, Actual Delivery and Actual Cumulative Delivery. However, I'm not sure on the formula to be used for Actual Sales which is based on Year_1 and Actual Delivery & Actual Cumulative Delivery which is on Year_2.

What should be added in Dimensions?

Sample data as under:

Year_1	Year_2	Actual Sales	Actual Delivery	Actual Cum. Delivery
2000	2001	100	100	100
2001	2002	200	150	250
2002	2002	300	150	400
2002	2003	200	250	650
2003	2000	150	200	850

[maxgro]

I think is better to change the table

LOAD

  Year_1 as Year,

    //Year_2,

    sum([Actual Sales]) as [Actual Sales]

    //[Actual Delivery],

    //[Actual Cum. Delivery]

FROM

[https://community.qlik.com/thread/181345]

(html, codepage is 1252, embedded labels, table is @1)

group by Year_1;

join LOAD

  //Year_1 as Year,

    Year_2 as Year,

    //[Actual Sales],

    sum([Actual Delivery]) as [Actual Delivery],

    sum([Actual Cum. Delivery]) as [Actual Cum. Delivery]

FROM

[https://community.qlik.com/thread/181345]

(html, codepage is 1252, embedded labels, table is @1)

group by Year_2;

and then the chart is simple

1 dimension Year

3 expression sum(...)