Creating a Line chart which shows peak values from a column for each month?

[rt_new_user]

Hi Qlik users

Can anyone help me with this requirement please?

I have a source data which is attached in a excel sheet. Requirement is to plot a line graph on monthly basis plotting peak value of throughput on each link 1 and link 2. Source data has a weekly and daily break down too for example:

Date	Week	Link1	Link2	Total	Year	Month
7/04/2013	15	14,100,892	14,310,199	28,411,091	2013	4
8/04/2013	15	15,209,665	8,597,185	23,806,850	2013	4
9/04/2013	15	16,235,045	11,249,548	27,484,592	2013	4
10/04/2013	15	14,901,235	12,821,364	27,722,598	2013	4
11/04/2013	15	16,558,743	10,496,901	27,055,644	2013	4
12/04/2013	15	13,278,023	9,572,060	22,850,083	2013	4
13/04/2013	15	15,591,720	8,713,675	24,305,395	2013	4
14/04/2013	16	13,373,035	9,142,064	22,515,099	2013	4
15/04/2013	16	16,079,632	10,944,456	27,024,088	2013	4
16/04/2013	16	16,049,943	8,821,569	24,871,512	2013	4
17/04/2013	16	17,754,935	11,346,522	29,101,457	2013	4
18/04/2013	16	19,439,135	10,302,640	29,741,776	2013	4
19/04/2013	16	16,326,619	11,336,960	27,663,579	2013	4
20/04/2013	16	19,197,952	10,116,814	29,314,766	2013	4
21/04/2013	17	14,186,104	9,557,832	23,743,936	2013	4
22/04/2013	17	13,635,805	10,439,441	24,075,245	2013	4
23/04/2013	17	12,957,849	12,983,068	25,940,917	2013	4
24/04/2013	17	10,111,022	9,595,168	19,706,190	2013	4
25/04/2013	17	11,903,729	12,835,190	24,738,919	2013	4
26/04/2013	17	11,850,439	10,303,617	22,154,056	2013	4
27/04/2013	17	9,968,335	8,513,604	18,481,939	2013	4
28/04/2013	18	10,459,080	10,075,961	20,535,041	2013	4
29/04/2013	18	9,557,329	8,201,043	17,758,372	2013	4
30/04/2013	18	10,263,615	8,653,946	18,917,561	2013	4
1/05/2013	18	8,738,216	8,114,808	16,853,024	2013	5
2/05/2013	18	12,947,411	9,014,871	21,962,282	2013	5
3/05/2013	18	8,864,129	8,481,556	17,345,686	2013	5
4/05/2013	18	8,864,129	8,481,556	17,345,686	2013	5
5/05/2013	19	14,766,328	10,003,388	24,259,124	2013	5
6/05/2013	19	12,752,650	14,482,708	23,887,866	2013	5
7/05/2013	19	11,635,019	13,997,367	22,604,914	2013	5
8/05/2013	19	12,868,805	13,224,409	23,622,160	2013	5
9/05/2013	19	13,664,729	13,870,300	24,196,229	2013	5
10/05/2013	19	14,578,749	11,696,608	22,411,807	2013	5

So on the graph on the x axis i want Month (but not as a number but as text Jan feb etc), so for example looking for the month February i.e Month 2, the peak value should be shown  for link 1 and link 2 on two separate lines from the months of feb (feb will have entries from week 5 till 9)

On y axis i want throughput but in Mbps (data source has it in bits for example 14,578,749 bits which on the graph should be converted into Mbits using formula by dividing by 1048576 as 1Mbits = 1048576bits hence 14,578,749/1,048,576 = 13.9 Mbits)

Once monthly graph is ready i'd like to drill down to Weekly peaks followed by daily.

Many thanks in advance for any help i could get.

[jyothish8807 · Accepted solution]

HI Ravi,

In the script I have created an addition field called New_Month which will convert the dates into month.So I will get Months ranging from Jan to Dec. When you will select an particular date or Year it will show corresponding to that.

2. To disable the heading simply untick show title in chart option in the general Tab  and in Caption tab you can give a heading.

PFA

Regards

KC

[rt_new_user]

Thanks Mate

Got it working.

Just a minor adjustment i need to do. In my script i am using

month(Date) as Month,

week(Date) as [Week no.]

as suggested by you and now i can see months of the year selected. months are displayed as Jan Feb Mar etc. how can i display them as Jan 14 Feb 14 Mar 14 etc

when i used this command in my script earlier i had them as jan 14 feb 14

Date(Date,'MMM yy') as Month,

thanks

[jyothish8807]

Hi Ravi,

PFA the updated one.

Regards

KC

[rt_new_user]

Done thanks a lot to you and everyone who contributed..