0

I know a similar question was asked before, but it wasn't answered. I have a chart where I use week numbers as its X-axis, that are retrieved as part of the SQL query.

It works fine up until a new year starts. In such a case, even though the weeks are ordered correctly when retrieved (for example, 49, 50, 51, 52, 1, 2, 3), they appear on the axis numerically ordered: 1, 2, 3, 49, 50, 51, 52.

Is there a way to fix that?

The relevant sql query part is:

SELECT DATEPART(year, start_charge_time),
       DATEPART(week, start_charge_time) week_num,
       COUNT(*) num_sessions
  FROM parking_log
GROUP BY DATEPART(year, start_charge_time),
         DATEPART(week, start_charge_time)
ORDER BY DATEPART(year, start_charge_time),
         DATEPART(week, start_charge_time)

The Chart is:

    Dim SeriesParkingRev As Series
    SeriesParkingRev = ChartParkingSummery.Series("SeriesParkingRev")

    ' Set series chart type
    SeriesParkingRev.ChartType = SeriesChartType.Line
    SeriesParkingRev.MarkerStyle = MarkerStyle.Square
    SeriesParkingRev.MarkerSize = 10
    SeriesParkingRev.BorderWidth = 3
    SeriesParkingRev.Color = Color.Red
    SeriesParkingRev.IsValueShownAsLabel = True
    SeriesParkingRev.IsVisibleInLegend = True
    '' Set series members names for the X and Y values 
    SeriesParkingRev.XValueMember = "week_num"
    SeriesParkingRev.YValueMembers = "total_charge"
    SeriesParkingRev.LegendText = "Parking Revenue"

    '' --------------------------

    ' Create the destination series and add it to the chart
    'Dim SeriesParkingTime As New Series("SeriesParkingTime")
    'ChartParkingSummery.Series.Add(SeriesParkingTime)

    Dim SeriesParkingTime As Series
    SeriesParkingTime = ChartParkingSummery.Series("SeriesParkingTime")

    ' Ensure the destination series is a Line or Spline chart type
    SeriesParkingTime.ChartType = SeriesChartType.Line
    SeriesParkingTime.MarkerStyle = MarkerStyle.Diamond
    SeriesParkingTime.MarkerSize = 12
    SeriesParkingTime.BorderWidth = 3
    SeriesParkingTime.IsValueShownAsLabel = True
    SeriesParkingTime.Color = Color.Blue

    ' Assign the series to the same chart area as the column chart
    SeriesParkingTime.ChartArea = ChartParkingSummery.Series("SeriesParkingRev").ChartArea

    ' Assign this series to use the secondary axis and set its maximum to be 100%
    SeriesParkingTime.YAxisType = AxisType.Secondary

    ChartParkingSummery.Series("SeriesParkingTime").XValueMember = "week_num"
    ChartParkingSummery.Series("SeriesParkingTime").YValueMembers = "avg_time"
    ChartParkingSummery.Series("SeriesParkingTime").LegendText = "Average Time"

    '' ----------------------------------------

    'Dim SeriesCars As New Series("SeriesCars")
    'ChartParkingSummery.Series.Add(SeriesCars)

    Dim SeriesCars As Series
    SeriesCars = ChartParkingSummery.Series("SeriesCars")

    SeriesCars.ChartType = SeriesChartType.Point
    SeriesCars.MarkerStyle = MarkerStyle.Triangle
    SeriesCars.MarkerSize = 8
    SeriesCars.BorderWidth = 3
    SeriesCars.IsValueShownAsLabel = True

    SeriesCars.MarkerStyle = MarkerStyle.Triangle
    SeriesCars.MarkerSize = 15
    SeriesCars.MarkerColor = Color.Green

    ChartParkingSummery.Series("SeriesCars").XValueMember = "week_num"
    ChartParkingSummery.Series("SeriesCars").YValueMembers = "num_cars"
    ChartParkingSummery.Series("SeriesCars").LegendText = "Cars"

    '' ----------------------------------------

    ChartParkingSummery.Legends.Add(New Legend("Default"))

    ' Set legend style
    ChartParkingSummery.Legends("Default").LegendStyle = LegendStyle.Table

    ' Set table style if legend style is Table

    ' Set legend docking
    ChartParkingSummery.Legends("Default").Docking = Docking.Right

    ' Set legend alignment
    ChartParkingSummery.Legends("Default").Alignment = StringAlignment.Center

    ChartParkingSummery.Titles.Add("Revenue, Avg Time & Cars")
Guy
  • 325
  • 1
  • 3
  • 18
  • You probably have a chart style that will do that naturally, not like a bar or scatter chart. What type of chart is it? Do you have any code? – Kat Jan 19 '15 at 19:45
  • 1
    since week of year is relative, an actual date for ordering the data might work better – Ňɏssa Pøngjǣrdenlarp Jan 19 '15 at 19:47
  • The ordering of the query is not a problem, as I use year, week. The problem is that the chart ignores that order – Guy Jan 19 '15 at 19:49
  • 1
    @Plutonix Either that or store the week numbers in the `DataPoint` as 50, 51, 52, 53, 54, etc; you could then use some custom axis labels to "reset" the display at week 53. Having some example code is necessary to provide a good answer, though. – mmathis Jan 19 '15 at 19:49

0 Answers0