Bar chart from datasource with multiple columns per axis

5 posts, 0 answers
  1. Shawn
    Shawn avatar
    42 posts
    Member since:
    Jul 2016

    Posted 13 Feb Link to this post

    I have a bar chart that is getting its data from my controller in the form of Json data as shown below:

    [
    {"SiteId":1,"Hour":9,"Quantity":9.0000,"Value":43.0500,"NumCustomers":5},
    {"SiteId":1,"Hour":10,"Quantity":18.0000,"Value":43.6900,"NumCustomers":8},
    {"SiteId":1,"Hour":11,"Quantity":7.0000,"Value":22.4200,"NumCustomers":3},
    {"SiteId":1,"Hour":14,"Quantity":1.0000,"Value":1.0000,"NumCustomers":1}
    {"SiteId":2,"Hour":9,"Quantity":10.0000,"Value":48.0500,"NumCustomers":7},
    {"SiteId":2,"Hour":10,"Quantity":20.0000,"Value":83.6900,"NumCustomers":11},
    {"SiteId":2,"Hour":11,"Quantity":9.0000,"Value":27.4200,"NumCustomers":5},
    {"SiteId":2,"Hour":14,"Quantity":3.0000,"Value":11.0000,"NumCustomers":4}
    {"SiteId":3,"Hour":9,"Quantity":7.0000,"Value":33.0500,"NumCustomers":4},
    {"SiteId":3,"Hour":10,"Quantity":16.0000,"Value":35.6900,"NumCustomers":7},
    {"SiteId":3,"Hour":11,"Quantity":7.0000,"Value":22.4200,"NumCustomers":3},
    {"SiteId":3,"Hour":14,"Quantity":2.0000,"Value":5.0000,"NumCustomers":2}
    ]

     

    As you can see, there are multiple sites for the same hours.  The horizontal axis should represent each hour from the data.  Each hour should have a column representing each site, very similar to the 'Column Chart' example on your site.  The code example on the site shows the series data like this:

    .Series(series => {
        series.Column(new double[] { 3.907, 7.943, 7.848, 9.284, 9.263, 9.801, 3.890, 8.238, 9.552, 6.855 }).Name("India");
        series.Column(new double[] { 4.743, 7.295, 7.175, 6.376, 8.153, 8.535, 5.247, -7.832, 4.3, 4.3 }).Name("Russian Federation");
        series.Column(new double[] { 0.010, -0.375, 1.161, 0.684, 3.7, 3.269, 1.083, -5.127, 3.690, 2.995 }).Name("Germany");
        series.Column(new double[] { 1.988, 2.733, 3.994, 3.464, 4.001, 3.939, 1.333, -2.245, 4.339, 2.727 }).Name("World");
    })

     

    My current code that shows the hourly totals for 1 site is listed below, but I'd like this to work for multiple sites as shown the in above collection of Json data. What is the best way to accomplish this?

    My current code:

        @(Html.Kendo().Chart<CBECloudBO2.ViewModels.ChartHourlySales>()
                .Name("chartHourlySales")
                .Title("Hourly Sales")
                .DataSource(datasource => datasource
                    .Read(read => read.Action("Chart_HourlySales", "Home")))
                .Legend(legend => legend
                    .Visible(false))
     
                .ChartArea(chartArea => chartArea
                    .Background("transparent"))
     
        .Series(series =>
        {
            series.Column(p => p.Value).Name("Total Sales");
        })
        .CategoryAxis(axis => axis
            .Name("series-axis")
            .Line(line => line.Visible(false))
        )
        .CategoryAxis(axis => axis
            .Name("label-axis")
            .Categories(p => p.Hour)
        )
        .ValueAxis(axis => axis
            .Numeric()
            .AxisCrossingValue(0, int.MinValue)
        )
        .Tooltip(tooltip => tooltip
            .Visible(true)
            .Format("{0}%")
            .Template("#= series.name #: #= value #")
        )
    )

     

    Thanks,

    Shawn

  2. Tsvetina
    Admin
    Tsvetina avatar
    2076 posts

    Posted 15 Feb Link to this post

    Hello Shawn,

    Looking at your data, it looks like it would be best to group it by SiteID. This will show one column per each site id in each category (in your case, this will be Hour I guess). You can see a demo with a grouped column chart here:
    Bar Charts / Binding to grouped data

    Regards,
    Tsvetina
    Telerik by Progress
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  3. Shawn
    Shawn avatar
    42 posts
    Member since:
    Jul 2016

    Posted 15 Feb in reply to Tsvetina Link to this post

    Tsvetina,

    The .Group did the trick.  Thanks for the reply.  However I am seeing an interesting issue with where the column data is actually being display.  The 'CBE Live Demo' data is all correct based on each hour the data was recorded.  However, the second site 'Townstal Road Garage' was recorded in hour 12, yet is being displayed at hour 0.  Below is my JSON data and the code implementing the chart.

    [
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":0,"Quantity":11.0000,"Value":119.0700,"NumCustomers":8},
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":1,"Quantity":6.0000,"Value":86.1500,"NumCustomers":5},
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":2,"Quantity":5.0000,"Value":18.6500,"NumCustomers":5},
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":3,"Quantity":6.0000,"Value":220.4500,"NumCustomers":6},
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":5,"Quantity":9.0000,"Value":56.1500,"NumCustomers":8},
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":6,"Quantity":23.0000,"Value":229.1500,"NumCustomers":19},
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":7,"Quantity":159.0000,"Value":1204.3500,"NumCustomers":112},
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":8,"Quantity":131.0000,"Value":860.5400,"NumCustomers":100},
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":9,"Quantity":134.0000,"Value":1232.5000,"NumCustomers":95},
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":10,"Quantity":208.0000,"Value":1281.8400,"NumCustomers":135},
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":11,"Quantity":210.0000,"Value":1022.2600,"NumCustomers":130},
    {"SiteId":1,"SiteName":"Townstal Road Garage","Hour":12,"Quantity":8.0000,"Value":28.5600,"NumCustomers":3},
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":12,"Quantity":154.0000,"Value":1193.5500,"NumCustomers":106},
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":13,"Quantity":230.0000,"Value":933.4500,"NumCustomers":139},
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":14,"Quantity":169.0000,"Value":893.4100,"NumCustomers":113},
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":15,"Quantity":210.0000,"Value":1204.1800,"NumCustomers":118},
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":16,"Quantity":208.0000,"Value":1402.3400,"NumCustomers":145},
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":17,"Quantity":171.0000,"Value":1233.3000,"NumCustomers":107},
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":18,"Quantity":205.0000,"Value":1418.8400,"NumCustomers":112},
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":19,"Quantity":143.0000,"Value":911.1400,"NumCustomers":92},
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":20,"Quantity":157.0000,"Value":829.1500,"NumCustomers":83},
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":21,"Quantity":79.0000,"Value":635.3000,"NumCustomers":51},
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":22,"Quantity":88.0000,"Value":328.6400,"NumCustomers":37},
    {"SiteId":4,"SiteName":"CBE Live Demo","Hour":23,"Quantity":58.0000,"Value":302.0500,"NumCustomers":31}
    ]

     

    As you can see by the bold text above, there are two chunks of data for hour 12.  One for site 1 and one for site 4.  I would expect the two columns to appear above the hour 12 on the chart, but they do not.  See attached screen shot.

    Here is my chart implementation:

    <div class="panel panel-default">
        <div class="demo-section k-content wide">
            @(Html.Kendo().Chart<CBECloudBO2.ViewModels.ChartHourlySales>()
                    .Name("chartHourlySales")
                    .Title("Hourly Sales (All Sites)")
                    .DataSource(datasource => datasource
                        .Read(read => read.Action("Chart_HourlySales", "Home"))
                        .Group(group => group.Add(model => model.SiteName))
                        .Sort(sort => sort.Add(model => model.Hour).Ascending())
                        )
                    .Legend(legend => legend.Visible(true))
                    .ChartArea(chartArea => chartArea
                    .Background("transparent"))
     
            .Series(series =>
            {
                series.Column(model => model.Value).Name("#= group.value #");
            })
            .CategoryAxis(axis => axis
                .Name("series-axis")
                .Line(line => line.Visible(false))
            )
            .CategoryAxis(axis => axis
                .Name("label-axis")
                .Categories(p => p.Hour)
            )
            .ValueAxis(axis => axis
                .Numeric()
            //.Labels(labels => labels.Format("{0}%"))
                .AxisCrossingValue(0, int.MinValue)
            )
            .Tooltip(tooltip => tooltip
                .Visible(true)
                .Format("{0}%")
                .Template("#= series.name #: #= value #")
            )
            )
        </div>
    </div>

     

    Regards,

    Shawn

  4. Tsvetina
    Admin
    Tsvetina avatar
    2076 posts

    Posted 17 Feb Link to this post

    Hi Shawn,

    This post explains why you observe this behavior. To summarize the instructions, you either need to provide a matching data item from both groups for each category, or you can use series.categoryField property instead of the categoryAxis.field one. You can see a JS version of the same chart with this setting applied here.

    Regards,
    Tsvetina
    Telerik by Progress
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  5. Shawn
    Shawn avatar
    42 posts
    Member since:
    Jul 2016

    Posted 17 Feb in reply to Tsvetina Link to this post

    Tsvetina,

    Thanks for the links.  I got it working by using the 'matching data' method, but I converted to use the CategoryField.  Works great now.

    Regards,

    Shawn

Back to Top