Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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(...)