VB.net setting negitive values to red and positive to blue in a chart

Jason
Jason used Ask the Experts™
on
Can anyone help with code to change the bar color to red if the number is negative and to blue if positive in a chart
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Engineer
Commented:
I will assume we're talking about a standard .NET chart control.

There's a couple of options.

1) I was hoping to connect a column from a datatable directly to the color of the datapoints but didn't find an option for that. Still I want to share the code, it may come in handy later.
        Dim dt As New DataTable
        dt.Columns.AddRange({New DataColumn("x", GetType(Integer)), New DataColumn("y", GetType(Integer)), New DataColumn("color", GetType(String))})
        dt.Columns("color").Expression = "IIF(y < 0, 'red', 'blue')"
        Dim r As New Random
        For x As Integer = 1 To 10
            dt.Rows.Add({x, r.Next(-5, 6)})
        Next
        Chart1.Series(0).Points.DataBind(dt.Rows, "x", "y", "color=color")
        Chart1.Series(0).ToolTip = "#CUSTOMPROPERTY(color)"

Open in new window

As you can see, you can show that label in the tooltip (and some other properties) but in this case it seems not much use.
capture1
2) you can loop through the data points and set the colors individually:
        Dim dt As New DataTable
        dt.Columns.AddRange({New DataColumn("x", GetType(Integer)), New DataColumn("y", GetType(Integer)), New DataColumn("color", GetType(String))})
        dt.Columns("color").Expression = "IIF(y < 0, 'red', 'blue')"
        Dim r As New Random
        For x As Integer = 1 To 10
            dt.Rows.Add({x, r.Next(-5, 6)})
        Next
        Chart1.Series(0).Points.DataBind(dt.Rows, "x", "y", "color=color")
        Chart1.Series(0).ToolTip = "#CUSTOMPROPERTY(color)"
        For Each p As DataPoint In Chart1.Series(0).Points
            p.Color = IIf(p.YValues(0) < 0, Color.Red, Color.Blue)
        Next

Open in new window

I left the Tooltip in there but that can be taken out of course if you don't want it.
capture2
3) You can split the data into 2 series which should be mutually exclusive:
        Dim dt As New DataTable
        dt.Columns.AddRange({New DataColumn("x", GetType(Integer)), New DataColumn("y", GetType(Integer))})
        Dim r As New Random
        For x As Integer = 1 To 10
            dt.Rows.Add({x, r.Next(-5, 6)})
        Next
        Chart1.Series.Clear()
        Chart1.Series.Add("negative")
        Chart1.Series(0).Color = Color.Red
        Chart1.Series(0).Points.DataBind(dt.Select("y < 0"), "x", "y", "")
        Chart1.Series.Add("positive")
        Chart1.Series(1).Color = Color.Blue
        Chart1.Series(1).Points.DataBind(dt.Select("y >= 0"), "x", "y", "")

Open in new window

capture3

Author

Commented:
Hey Robert

I couldn't get the code to display red and blue

Here is the code I am using.
Any help would be great
 Private Sub VenueData()

        Dim dt As DataTable
        dt = CreateVenueDataTable()

        ' frmBetAssist.Chart2.Titles.Add("Title1")
        frmBetAssist.Chart2.Titles("Title1").Text = "Profit By Venue"
        'frmBetAssist.Chart2.Titles("Title1").DockedToChartArea = Nothing

        'Set the DataSource property of the Chart control to the DataTabel
        frmBetAssist.Chart2.DataSource = dt

        'Give two columns of data to Y-axle
        frmBetAssist.Chart2.Series(0).YValueMembers = "Profit"

        'Set the X-axle as date value
        frmBetAssist.Chart2.Series(0).XValueMember = "Venue"


        frmBetAssist.Chart2.Series(0).Points.DataBind(dt.Rows, "Venue", "Profit", "color=color")
        'frmBetAssist.Chart2.Series(0).ToolTip = "#CUSTOMPROPERTY(color)"
        For Each p As DataPoint In frmBetAssist.Chart2.Series(0).Points
            p.Color = IIf(p.YValues(0) < 0, Color.Red, Color.Blue)
        Next




        'Bind the Chart control with the setting above
        'frmBetAssist.Chart2.DataBind()

    End Sub

    Private Function CreateVenueDataTable() As DataTable
        Dim str As String = "C:\BA\ChartData\ChartData.txt"
        Dim list As List(Of String) = New List(Of String)()
        Dim list2 As List(Of String) = New List(Of String)()


        If (System.IO.File.Exists(str)) Then
            list.Clear()
            list.AddRange(System.IO.File.ReadAllLines(str))
            list2 = list
            Dim profit As Decimal = 0
            'Create a DataTable as the data source of the Chart control
            Dim dt As New DataTable

            'dt.Columns.Add("Venue")
            'dt.Columns.Add("Profit")
            dt.Columns.AddRange({New DataColumn("Venue", GetType(String)), New DataColumn("Profit", GetType(Decimal)), New DataColumn("color", GetType(String))})
            dt.Columns("color").Expression = "IIF(Profit < 0, 'red', 'blue')"

            For i As Integer = 0 To list.Count - 1
                Dim parts As String() = list(i).Split(New Char() {";"c})
                Dim valueToSearch As String = parts(2)
                Dim exists As Boolean = Nothing
                For Each dRow As DataRow In dt.Rows
                    If Convert.ToString(dRow(0)).Contains(valueToSearch) = True Then
                        exists = True
                        Exit For
                    ElseIf Convert.ToString(dRow(0)).Contains(valueToSearch) = False Then
                        exists = False
                    End If
                Next
                If Not exists Then
                    For x As Integer = 0 To list2.Count - 1
                        Dim parts1 As String() = list2(x).Split(New Char() {";"c})
                        If parts(2) = parts1(2) Then
                            profit = profit + parts1(5)
                        End If
                    Next
                    Dim dr As DataRow

                    'Add rows to the table which contains some random data for demonstration
                    dr = dt.NewRow()
                    dr("Venue") = parts(2)
                    dr("Profit") = profit
                    dt.Rows.Add(dr)
                End If
            Next
            Return dt
        End If

    End Function

Open in new window

Robert SchuttSoftware Engineer

Commented:
The DataBind call replaces the statements on lines 11, 14 & 17 so those should not be necessary anymore.

Do you get an errror?

Can you give a few example lines from the text file?
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Author

Commented:
Here's the file
ChartData.txt

Author

Commented:
Works now thanks for the help

Author

Commented:
Great help thks
Robert SchuttSoftware Engineer

Commented:
OK, great! There are some other issues in your code. Are you aware/is this a work in progress or would you like me to share my thoughts on that?

Author

Commented:
by all means any help would be great
Robert SchuttSoftware Engineer

Commented:
Couple of things:

1) 'profit' is only initialized once, should that not be reset inside the loop, say before line 66?

2) The comparison done on datarows seems to me dangerous, what if you ever get a key that is part of another key like "Sydney" and "Sydney South"? Probably better to check the specific field there as well.

That whole bit of code can probably be replaced by a nice bit of LINQ, now that is not my strongest subject but there are other experts here that dream in that sort of code ;-)

I would probably do something more like this:
    Private Function CreateVenueDataTable() As DataTable
        Dim str As String = "C:\BA\ChartData\ChartData.txt"
        Dim list As List(Of String) = New List(Of String)()

        'Create a DataTable as the data source of the Chart control
        Dim dt As New DataTable
        dt.Columns.AddRange({New DataColumn("Venue", GetType(String)), New DataColumn("Profit", GetType(Decimal))})
        If (System.IO.File.Exists(str)) Then
            list.Clear()
            list.AddRange(System.IO.File.ReadAllLines(str))
            Dim profit As Decimal = 0
            Dim dict As New Dictionary(Of String, Decimal)
            For i As Integer = 0 To list.Count - 1
                Dim parts As String() = list(i).Split(New Char() {";"c})
                Dim valueToSearch As String = parts(2)
                profit = parts(5)
                If dict.ContainsKey(valueToSearch) Then
                    dict(valueToSearch) += profit
                Else
                    dict.Add(valueToSearch, profit)
                End If
            Next
            For Each key As String In dict.Keys
                Dim dr As DataRow

                'Add rows to the table which contains some random data for demonstration
                dr = dt.NewRow()
                dr("Venue") = key
                dr("Profit") = dict(key)
                dt.Rows.Add(dr)
            Next
        End If
        Return dt
    End Function

Open in new window

Maybe the file won't get very big but going through it several times for each line can get very slow. Dictionary already has the ContainsKey out of the box.
Robert SchuttSoftware Engineer

Commented:
Actually, Dictionary is not even necessary as DataTable has a Select that can tell you if the key is already in there.
    Private Function CreateVenueDataTable() As DataTable
        Dim str As String = "C:\BA\ChartData\ChartData.txt"
        Dim list As List(Of String) = New List(Of String)()

        'Create a DataTable as the data source of the Chart control
        Dim dt As New DataTable
        dt.Columns.AddRange({New DataColumn("Venue", GetType(String)), New DataColumn("Profit", GetType(Decimal))})
        If (System.IO.File.Exists(str)) Then
            list.Clear()
            list.AddRange(System.IO.File.ReadAllLines(str))
            Dim profit As Decimal = 0
            For i As Integer = 0 To list.Count - 1
                Dim parts As String() = list(i).Split(New Char() {";"c})
                Dim valueToSearch As String = parts(2)
                profit = parts(5)
                Dim drs() As DataRow = dt.Select("Venue = '" & valueToSearch.Replace("'", "''") & "'")
                If drs.Length > 0 Then
                    drs(0)("Profit") += profit
                Else
                    Dim dr As DataRow

                    'Add rows to the table which contains some random data for demonstration
                    dr = dt.NewRow()
                    dr("Venue") = valueToSearch
                    dr("Profit") = profit
                    dt.Rows.Add(dr)
                End If
            Next
        End If
        Return dt
    End Function

Open in new window

Author

Commented:
both work a treat
Is one way better than the other??
Robert SchuttSoftware Engineer

Commented:
Not really, I just introduced the Dictionary to be able to quickly see if a key already exists before realizing that DataTable already provides similar function. In a production environment I would probably run some tests to see if either of them is significantly faster or uses less memory, but in this case I'm guessing that will not be a major concern... (yet?)

Author

Commented:
cool thx again for you advice and help
Jason

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial