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

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
JasonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robert SchuttSoftware EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JasonAuthor 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 EngineerCommented:
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?
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

JasonAuthor Commented:
Here's the file
ChartData.txt
JasonAuthor Commented:
Works now thanks for the help
JasonAuthor Commented:
Great help thks
Robert SchuttSoftware EngineerCommented:
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?
JasonAuthor Commented:
by all means any help would be great
Robert SchuttSoftware EngineerCommented:
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 EngineerCommented:
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

JasonAuthor Commented:
both work a treat
Is one way better than the other??
Robert SchuttSoftware EngineerCommented:
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?)
JasonAuthor Commented:
cool thx again for you advice and help
Jason
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.