?
Solved

VB chart with X axis in week numbers

Posted on 2015-01-03
3
Medium Priority
?
309 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 84

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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Loops Section Overview
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

864 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