DotNet.Highcharts: Cost not plotted against the correct date

Question

I'm creating a DotNet.Highcharts chart that is using data from two tables: Expenditures and Incomings. I am using an SQL statement to create a DataTable for each. The first (for Incomings) called Dt contains IncCost and IncDate. The second (for Expenditures) called Dt2 contains ExpCost and ExpDate.

I am able to plot IncCost against IncDate and ExpCost against ExpDate. The problem arises when I try to concatenate IncDate and ExpDate (final) as the cost for IncCost and ExpCost is plotting against the position of the date in (final), and not the actual date it is related to.

Here are the calls I use to create each DataTable:

SqlDataAdapter Adp = new SqlDataAdapter("select CONVERT(DATETIME, IncDate, 103) AS IncDate, SUM(IncCost) AS IncCost from Incomings GROUP BY CONVERT(DATETIME, IncDate, 103) ORDER BY CONVERT(DATETIME, IncDate, 103)", con);

SqlDataAdapter Adp2 = new SqlDataAdapter("select CONVERT(DATETIME, ExpDate, 103) AS ExpDate, SUM(ExpCost) AS ExpCost from Expenditures GROUP BY CONVERT(DATETIME, ExpDate, 103) ORDER BY CONVERT(DATETIME, ExpDate, 103)", con);

Here is the code for calling each of these from the datatable

var dateArr = Dt.AsEnumerable().Select(r => r.Field<DateTime>("IncDate")).ToArray();
var objectArr = Dt.AsEnumerable().Select(r => r.Field<object>("IncCost")).ToArray();

var dateArr2 = Dt2.AsEnumerable().Select(r => r.Field<DateTime>("ExpDate")).ToArray();
var objectArr2 = Dt2.AsEnumerable().Select(r => r.Field<object>("ExpCost")).ToArray();   

var res = dateArr.Concat(dateArr2).ToArray();
var final = res.Select(d => d.ToString(@"dd\/MM\/yyyy")).ToArray();

And here is the code I use to create my highchart:

Highcharts chart = new Highcharts("graph")
  .SetTitle(new Title { Text = "Incoming Stats" })
  .SetXAxis(new[] { new XAxis { Categories = final } })
  .SetYAxis(new YAxis { Title = new YAxisTitle { Text = "Amount Incoming" } })
  .SetSeries(new[]
    {
      new Series { Name = "inc", Data = new Data(objectArr)},
      new Series { Name = "exp", Data = new Data(objectArr2) }
    });

As you can see, the series are being created but they are being plotted against the number in the array, and not against the date it should be linked to.

I'm not sure how close or far I am to a solution but any help is appreciated. Here is my incomings datatable

Here is my expenditure datatable

Genuinely don't know how to go about this. any information is greatly appreciated

jsanalytics · Accepted Answer · 2016-03-08T01:45:22.467

Your Y values are completely disconnected from X values. In order to achieve what you want, you need to restructure your data as below:

        DateTime dt = new DateTime(2016, 2, 27);

        Highcharts chart = new Highcharts("graph")
           .SetTitle(new Title { Text = "Incoming Stats" })
           .SetXAxis(new XAxis { Type = AxisTypes.Datetime })
           .SetYAxis(new YAxis { Title = new YAxisTitle { Text = "Amount Incoming" } })
           .SetSeries(new[]
             {
              new Series { Name = "inc", Data = new Data(new object[,] { 
                  {dt , 23 }, 
                  {dt.AddDays(1) , 223 }, 
                  {dt.AddDays(2) , 51 }, 
                  {dt.AddDays(11) , 200 }, }) },
              new Series { Name = "exp", Data = new Data(new object[,] { 
                  {dt.AddDays(5) , 100 }, 
                  {dt.AddDays(6) , 23 }, 
                  {dt.AddDays(11) , 23 }, 
                  {dt.AddDays(19) , 35 }, 
                  {dt.AddDays(35) , 288 }, }) }
             });

EDIT: How to do it dynamically:

        object[,] data1 = new object[Dt.Rows.Count, 2];
        for (int i = 0; i < Dt.Rows.Count; i++)
        {
            data1[i, 0] = Dt.Rows[i]["IncDate"];
            data1[i, 1] = Dt.Rows[i]["IncCost"];
        }

        object[,] data2 = new object[Dt2.Rows.Count, 2];
        for (int i = 0; i < Dt2.Rows.Count; i++)
        {
            data2[i, 0] = Dt2.Rows[i]["ExpDate"];
            data2[i, 1] = Dt2.Rows[i]["ExpCost"];
        }

        Highcharts chart = new Highcharts("graph")
           .SetTitle(new Title { Text = "Incoming Stats" })
           .SetXAxis(new XAxis { Type = AxisTypes.Datetime })
           .SetYAxis(new YAxis { Title = new YAxisTitle { Text = "Amount Incoming" } })
           .SetSeries(new[]
             {
                    new Series { Name = "inc", Data = new Data(data1) },
                    new Series { Name = "exp", Data = new Data(data2) }
             });

Data retrieved from database:

Thank you for this although I was hoping for it to be dynamic and not manually enter the data — Stuart, Mar 07 '16 at 21:29
You can do it dynamically.... I'm pointing out what the core issue is here: you're using the wrong constructor overload `Data(object[] data)`, which causes X and Y for your `Series` to be completely disassociated from each other. Instead, use `Data(object[ , ] data)`, this way you have an array of [X,Y] pairs, which solves the problem you're having, as opposed to having two separate arrays [X] and [Y] that are unrelated to each other. Your data structure holding results have to have the same structure as shown above, no matter you get it dynamically or not. — jsanalytics, Mar 07 '16 at 22:06
Thank you for your help. Although I have no idea how I would restructure my code in the way you suggested to make it dynamic — Stuart, Mar 07 '16 at 22:09
brilliant thank you. I'll be able to award it to you in 13 hours — Stuart, Mar 08 '16 at 01:57
Thank you again, the bounty will be available in 3 hours. Is there a way I can edit my code that the cost is added on to the previous cost? So the line would continually move up instead of being up and down? — Stuart, Mar 08 '16 at 11:50
You would need to change your query accordingly, probably using a correlated sub-query to calculate the partial totals up to the current date. — jsanalytics, Mar 08 '16 at 12:13
If you search online for _subquery_ or _correlated subquery_ there's plenty of material. — jsanalytics, Mar 08 '16 at 12:31

DotNet.Highcharts: Cost not plotted against the correct date

1 Answers1

Linked