• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Little Law implementation on Queue Chart

kuldeepjainesl

kuldeepjainesl

Member
Hello Ninja

i am using the Queue chart to present the performance but now stuck with the requirement of implementing Little LAW to queue chart.

To explain this, i need to plot two line (Marked green in attached excel). The first one is the difference of last two data point but to find the second one i am required to plot the horizontal line from last data point of lower line and to catch the point where the line will intercept the upper one.

According to Little Law the Vertical line shows the queue while the horizontal line is the queue time.

Any help or idea is appriciated.
 

Attachments

  • Queue Chart.xlsx
    12.2 KB · Views: 14
kuldeepjainesl

You will need to make a few small changes

Change the chart type from Line to Scatter
Add a new Set of numbers for the revised X axis
Add two sets of numbers for the Horizontal and Vertical lines

The vertical lines X & Y Values are easy to calculate from the data
The Horizontal lines X & Y values can be calculated using some techniques described here
http://chandoo.org/wp/2013/02/08/formula-forensics-no-033/

Refer to a modified copy of your file here:
 

Attachments

  • Queue Chart_Hui.xlsx
    13.2 KB · Views: 11
Hi Kuldeep ,

You can make it more configurable ; check this file. Change the designated point , and the error bars shift accordingly.

I have not made the calculations for the slope between two points , which is why in some cases the X error bar is not exact , but this is a simple addition.

Also there is no error checking.

Narayan
 

Attachments

  • Queue Chart.xlsm
    20.3 KB · Views: 14
Thanks, Hui ,Narayan

Yot Both Guys are awesome...This is all i needed. Now i have two way of doing while i had no idea to achieve this. Now let me through it to my actual data template.

Just want a bit more clarification on Narayan statement " What do you mean by no error checking"

Narayan, However would like to explore a bit more on your solution, how can plot the exact X bar with data label for their amplitude.

Label addition is a bit important and i cound not see the feild in formmating to add these.:)

Regards,
 
Hi Kuldeep ,

The problem is in the following case , just as an instance :

Suppose we are looking at point 13 , whose two Y-values are 16 and 7 ; we need to plot the X error bar from the point 13 on the lower graph , till the line meets the upper graph at the same vertical value of 7.

Now the problem is that the upper graph has a Y-value of 5 at point 5 , and a Y-value of 8 at point 6.

We need to interpolate between these two points to find out how long the X error bar should be to meet the upper graph exactly on the line , neither overshooting which will happen if we select point 5 , nor undershooting if we select point 6.

To do this , we need to find out the slope of the line between points 5 and 6 , and see at what point the horizontal line will touch the line , somewhere in between points 5 and 6. The expression can be derived if you use similar triangles , and works out to ( the Y-value corresponding to the point 6 on the upper graph - the Y-value corresponding to the point from where we want the X error bar ) / ( the Y-value corresponding to the point 6 on the upper graph - the Y-value corresponding to the point 5 on the upper graph ) i.e. ( 8 - 7 ) / ( 8 - 5 ) i.e. 1/3. This extra length is to be added to the X length of 13 - 6 so that we get 7.33

If you want this to be done , let me know , since it will take more words to explain than to code !

The easiest way would be to have the mathematics as a separate function which can be called each time you wish to interpolate.

Narayan
 
I agree to this narayan,

This would be great if can be done else to explain everyone will become complex.

How ever i would need the data label on these error bars...Being this a VBA approach i am completely on you.

Regards,
 
Hi Kuldeep ,

Check the file now ; regarding your point about data label , I am not clear on what you want ; please explain with an example.

Narayan
 

Attachments

  • Queue Chart.xlsm
    21 KB · Views: 16
Wow...I don't have word to express thanks... You made my day once again. Thanks narayan

Data label of error bar example. I will have weeks on X axis and count on Y axis.

Now in this chart say at point 10, the vertical Error bar amplitude is 6 while the length of horizontal error bar is almost 5 weeks.

Requirement is just to add data value of error bar.

Regards,
 
Hi Kuldeep ,

It is somewhat late at night now ; I'll try my hand at it tomorrow morning. If I can come up with something , I'll get back to you.

Narayan
 
Thats it....Narayan....Thanks

Just a quick question, i will have a number of chart on the worksheet so would like learn what is required modify to configure the chart number.

In case you need to file please let me know as i can not attached that one here.

Regards,
 
Hi Kuldeep ,

We are referring to a particular chart by this statement :

ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)

where the index number of 1 in ChartObjects(1) refers to the first chart in your worksheet ; if you wish to refer to another chart , change this index number.

Of course , for each chart , you will have to create two additional series , data for which in your case are in the ranges G7 through G26 and H7 through H26.

Narayan
 
Tweaking the Error Bar formats a little bit gives the attached
 

Attachments

  • Queue Chart.xlsm
    19.8 KB · Views: 9
Narayan, somehow i am not able to accomplish this, The probable cause is lack of VBA awareness.

I have added the required helper series_1 and series_2 and modified the chart number to use.

Secondly tried to direct the code to the different worksheet from active sheet by in your code.

Number_of_points = Worksheets("Dashboard").ChartObjects("Chart 3").Chart.SeriesCollection(1).Points.Count

But stuck from this onward as my chart input data is being referred by dynamic ranges "For example ='Data Process'!$I$13:INDEX('Data Process'!$I$13:$I$63,COUNT('Data Process'!$E$13:$E$63))"

and i am getting no idea how to address these.

Regards,
 
Hi Kuldeep ,

If you can upload your workbook , I can do it for a second chart , and from there you can go ahead with the remaining charts.

Narayan
 
@kuldeepjainesl
Hi!
I think that NARAYANK991 knows his own email address, so next time please indicate where to look but don't specify even a masked address.
Regards!
 
Hello Narayan,

Any hope to get the revised version by today or may i have a tentative time so that i can plan to omit or include this in current month.
 
Hi Kuldeep ,

I can promise tomorrow , but if it is really urgent , let me know , and I'll try to get it done by tonight.

Narayan
 
No at all narayan, Please do this as per your priority. i hope if i can get this by 2:00 PM tomorrow so that l can further modify this for the remaining points.
 
You must log in or register to reply here.
Back
Top