Discussion Board for collaboration on QlikView Scripting.
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:
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.
Solved! Go to Solution.
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.
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
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
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
I am using line chart and this is what my script looks like in the script editor:
// WeekName(Date) as Week,
// Date(Date,'MMM yy') as Month,
MonthName(Date) as Month,
F16 as ,
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
>>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
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?
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