topic Re: Creating a Line chart which shows peak values from a column for each month? in QlikView

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

rt_new_user — Tue, 02 Dec 2014 03:33:45 GMT

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.

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

sushil353 — Tue, 02 Dec 2014 04:41:39 GMT

Hi,

Step1: in your script load add another field as

Date(Date,'MMM') as Month,

Step 2: Create a bar chart and in Dimension Tab click on Edit Group option.. in lower left corner..

There you can define a dill down group with Month>Week and Date dimension..
Clik on OK>OK

Use this drill group in your chart dimension..

Step3: In Expression Create two expression

Exp1: Sum(Link1/1048576)

Exp2: Sum(Link2/1048576)

HTH

Sushil

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

rt_new_user — Tue, 02 Dec 2014 05:31:25 GMT

Thanks Sushil

Step 1 done – thanks for that

The expression that you have suggested wouldn’t that give sum, but what i am looking for is peak or max of throughput for each week on each link. In the drill down – when looking at month view i want to look at peak throughput values from the sample of 30 days in that month. Then when looking at week view i want a peak value shown on the graph for those 7 days. Hopefully i am making sense in explaining my requirement.

Thanks again for your help

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

Anonymous — Wed, 03 Dec 2014 01:47:58 GMT

Hello, Ravi.

Instead of Date(Date, 'MMM'), you may want to use MonthName(Date) as Month if you want the month to be like 'dec 2013' and 'jan 2014'. You could use a similar WeekName(Date) as Week for the week dimension.

Create the hierarchic group as suggested by Sushil. I would use a line chart instead of a bar chart, but it's up to you.

As expressions, use these:

Link1 Peak: Max(Link1)/1048576

Link2 Peak: Max(Link2)/1048576

Regards,

- Bruno.

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

rt_new_user — Wed, 03 Dec 2014 02:52:30 GMT

Thanks Bruno

I am using line chart and this is what my script looks like in the script editor:

LOAD Date,

Week,

// WeekName(Date) as Week,

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

MonthName(Date) as Month,

Kent,

Exhibition,

Total,

Year,

F12,

F13,

F14,

Week1,

F16 as ,

Total1,

F21,

AA,

AB,

AC,

FROM

My graph looks something like this:

1. How to i limit the graph to only show last 12 months i.e from Oct 2013 to Oct 2014 – i have tried restricting Dimensions to shown 12 but it only has the option to show “first” not last on the dimensions tab of the graph properties?

2. Secondly why does the graph shows a label “Max at Kent” – i have only labelled expressions to show as legends such as Max at Kent, Max at Exhibition. How to i remove this label “Max at Kent” from the graph?

3. Expressions i have used for plotting peaks for month/week from the two links Kent and Exhibition is: For Kent - Max (Kent)/1048576, for Exhibition - Max (Exhibition)/1048576. I would also like to plot Total of Peaks from Kent and Exhibition for example if peak for Feb 2014 from Kent is 12 and exhibition is 14, i want total to show 12+14 = 26, what expression should i use for it please so that it only sums up the peaks from Kent and exhibition not the whole total?

Sorry about asking so many questions.

Thanks for your help

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

kushalthakral — Wed, 03 Dec 2014 03:15:59 GMT

Hi Ravi

>>There is an option of changing your Label, so for graph you can name the label as per your convenience.

>>For the 3rd point you can use the expression Max (Kent)/1048576+Max (Exhibition)/1048576

Regards

Kushal Thakral

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

jyothish8807 — Wed, 03 Dec 2014 03:33:49 GMT

Hi Ravi,

PFA

Hope it helps

Regards

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

rt_new_user — Wed, 03 Dec 2014 03:46:13 GMT

Hi Jyothish

Thanks for your qvw file.

how did you restrict your graph to only show jan-dec data firstly?

secondly how can you remove the heading "Max(Link1)/1048576" from your graph and only have it as a legend along with Max(Link2)/1048576?

Thanks

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

kushalthakral — Wed, 03 Dec 2014 03:51:45 GMT

Hi Ravi

Please find the document as per your requirement

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

rt_new_user — Wed, 03 Dec 2014 03:53:57 GMT

Thanks Kushal

Peak total worked ok with your suggestion.

i figured out why it was using Max at kent as a title, just had to uncheck one of the box on general tab which said show as title on chart

one of the question which remains now is how do i limit it to show only last 12 months and not the whole data set..?

Thanks for your help again

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

jyothish8807 — Wed, 03 Dec 2014 04:36:17 GMT

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

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

rt_new_user — Wed, 03 Dec 2014 05:02:23 GMT

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

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

jyothish8807 — Wed, 03 Dec 2014 05:17:34 GMT

Hi Ravi,

PFA the updated one.

Regards

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

rt_new_user — Wed, 03 Dec 2014 05:26:09 GMT

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