Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
rt_new_user
New Contributor II

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

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:

DateWeekLink1Link2TotalYearMonth
7/04/20131514,100,89214,310,19928,411,09120134
8/04/20131515,209,6658,597,18523,806,85020134
9/04/20131516,235,04511,249,54827,484,59220134
10/04/20131514,901,23512,821,36427,722,59820134
11/04/20131516,558,74310,496,90127,055,64420134
12/04/20131513,278,0239,572,06022,850,08320134
13/04/20131515,591,7208,713,67524,305,39520134
14/04/20131613,373,0359,142,06422,515,09920134
15/04/20131616,079,63210,944,45627,024,08820134
16/04/20131616,049,9438,821,56924,871,51220134
17/04/20131617,754,93511,346,52229,101,45720134
18/04/20131619,439,13510,302,64029,741,77620134
19/04/20131616,326,61911,336,96027,663,57920134
20/04/20131619,197,95210,116,81429,314,76620134
21/04/20131714,186,1049,557,83223,743,93620134
22/04/20131713,635,80510,439,44124,075,24520134
23/04/20131712,957,84912,983,06825,940,91720134
24/04/20131710,111,0229,595,16819,706,19020134
25/04/20131711,903,72912,835,19024,738,91920134
26/04/20131711,850,43910,303,61722,154,05620134
27/04/2013179,968,3358,513,60418,481,93920134
28/04/20131810,459,08010,075,96120,535,04120134
29/04/2013189,557,3298,201,04317,758,37220134
30/04/20131810,263,6158,653,94618,917,56120134
1/05/2013188,738,2168,114,80816,853,02420135
2/05/20131812,947,4119,014,87121,962,28220135
3/05/2013188,864,1298,481,55617,345,68620135
4/05/2013188,864,1298,481,55617,345,68620135
5/05/20131914,766,32810,003,38824,259,12420135
6/05/20131912,752,65014,482,70823,887,86620135
7/05/20131911,635,01913,997,36722,604,91420135
8/05/20131912,868,80513,224,40923,622,16020135
9/05/20131913,664,72913,870,30024,196,22920135
10/05/20131914,578,74911,696,60822,411,80720135

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.

1 Solution

Accepted Solutions
jyothish8807
Honored Contributor II

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

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

Best Regards,
KC
13 Replies
sushil353
Honored Contributor II

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

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

rt_new_user
New Contributor II

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

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

bruno_montenegr
Contributor III

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

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.

rt_new_user
New Contributor II

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

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,

,

W,

X,

Y,

Z,

AA,

AB,

AC,

AD

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

kushalthakral
Contributor II

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

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

jyothish8807
Honored Contributor II

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

Hi Ravi,

PFA

Hope it helps

Regards

KC

Best Regards,
KC
rt_new_user
New Contributor II

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

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

kushalthakral
Contributor II

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

Hi Ravi

Please find the document as per your requirement

rt_new_user
New Contributor II

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

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

Community Browser