Solved

VB chart with X axis in week numbers

Posted on 2015-01-03
3
265 Views
Last Modified: 2015-01-16
Dear Experts,

Is it possible to set the x axis of a vb chart to week numbers.

At the moment i set the x maximum to the current week number (as an integer) and the x minimum to the current week number minus 5 with an increment of 1.

Problem is that now we have passed year end i get negative numbers.

Eg.   -3 -2 -1 0 1
Need 50 51 52 53 1

The base data (week numbers extracted from dates) i have stored in an SQL table.

All help most welcome
0
Comment
Question by:SweetingA
3 Comments
 
LVL 78

Expert Comment

by:David Johnson, CD, MVP
ID: 40529447
you have to add
if weeknumber < 0 weeknumber = weeknumber + 52
0
 

Author Comment

by:SweetingA
ID: 40529455
I can't see how that will help when using vb charts, there are only a small number of things that i know can be set

X Axis minimum
X Axis maximum
X Axis interval

I am not sure if each interval can be set in a non sequential order

See my current code below

Dim sw as integer    
Dim ew as integer    
ew = DatePart(DateInterval.WeekOfYear, Date.Today, FirstDayOfWeek.Monday,
FirstWeekOfYear.FirstFourDays) + 1
sw = ew -6    
ChartStopsByDate.ChartAreas(0).AxisX.Minimum = sw    
ChartStopsByDate.ChartAreas(0).AxisX.Maximum = ew    
ChartStopsByDate.ChartAreas(0).AxisX.Interval = 1    
ChartStopsByDate.ChartAreas(0).AxisY.Minimum = 0    
ChartStopsByDate.ChartAreas(0).AxisY.Maximum = 60    
ChartStopsByDate.ChartAreas(0).AxisY.Interval = 10
0
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
ID: 40535296
Try adding this to the end of your code:
        Chart1.DataBind() ' needed here (if not called already) to be able to iterate over .Points collection
        For p As Integer = 0 To Chart1.Series(0).Points.Count - 1
            Dim pt As DataPoint = Chart1.Series(0).Points(p)
            If pt.XValue >= sw And pt.XValue <= ew Then
                ' get year and week from the Thursday in each week - see http://en.wikipedia.org/wiki/ISO_week_date
                Dim datThursday As Date = Date.Today.AddYears(intTestYears).AddDays(7 * pt.XValue + DayOfWeek.Thursday - Date.Today.AddYears(intTestYears).DayOfWeek)
                pt.AxisLabel = String.Format("{0}W{1:00}", datThursday.Year, DatePart(DateInterval.WeekOfYear, datThursday, FirstDayOfWeek.Monday, FirstWeekOfYear.FirstFourDays))
            End If
        Next

Open in new window

It's basically what David suggested, just a bit extended so you shouldn't have to revise this every year regarding 52/53...

Obviously for your requirement you don't need the string.format(), only
pt.AxisLabel = DatePart(DateInterval.WeekOfYear, datThursday, FirstDayOfWeek.Monday, FirstWeekOfYear.FirstFourDays)

Open in new window


PS: I may be off by a week because of the increment, you may want to use 7 * (pt.XValue - 1)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now