Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: Cluster of stacked bars in CCC Bar Chart?

  1. #1
    Join Date
    Apr 2014
    Posts
    9

    Default Cluster of stacked bars in CCC Bar Chart?

    Hi,

    I'm trying to make a bar chart showing a cluster of stacked bars, similar to the following (except, I only need to cluster two stacked bars in each group, not four):

    Name:  clustered_stacked.jpg
Views: 2427
Size:  25.1 KB

    I can make a stacked bar chart using a CCC Bar Chart component, but can't figure out how to add another in a cluster. I'm running this SQL query:

    Code:
    select
            date_produced
            , product
            , perc_of_weekly_demand
            , perc_of_weekly_sales
        from mytable
        order by date asc;
    which produces a table like this:

    date_produced product perc_of_weekly_demand perc_of_weekly_sales
    3/14/14 Hat .8 .85
    3/14/14 Glove .2 .15
    3/21/14 Shirt .7 .6
    3/21/14 Shoe .3 .4

    I'd like to plot a chart where the series is 'product', the category is 'date_produced', and there are two value columns: 'perc_of_weekly_demand', and 'perc_of_weekly_sales'. This way, I'd get a cluster of stacked bars where the x-axis has ticks for the date, and within each date, there is a cluster of two stacked bars (one for perc_of_weekly_demand and one for perc_of_weekly_sales).

    So far, I'm able to make a CCC Bar Chart, but I'm able to show only one stacked bar per date, not a cluster of two. I've tried playing around with the plot2 parameters in the CCC Bar Chart, but have not been able to figure out how to add a second stacked bar.

    Thanks for the help!

  2. #2
    Join Date
    Mar 2012
    Posts
    645

    Default

    There's a simillar stacked/grouped variant of bar charts which is not quite what (I think that) you want (http://jsfiddle.net/duarteleao/v9DSc/). It might provide some inspiration though.

    To do what (I think that) you want:

    1) Translate the source data:

    Code:
    crosstabMode: true,
    readers:      ['measure, date_produced, product, value']
    This will read the table into the following internal data format:

    Code:
    measure                date_produced   product    value
    --------------------------------------------------------
    perc_of_weekly_demand  3/14/14         Hat        .8  
    perc_of_weekly_sales   3/14/14         Hat        .85
    perc_of_weekly_demand  3/14/14         Glove      .2
    perc_of_weekly_sales   3/14/14         Glove      .15
    perc_of_weekly_demand  3/21/14         Shirt      .7  
    perc_of_weekly_sales   3/21/14         Shirt      .6
    perc_of_weekly_demand  3/21/14         Shoe       .3
    perc_of_weekly_sales   3/21/14         Shoe       .4
    The names I gave to the dimensions are arbitrary and need not match the ones in the CDA data metadata.
    The special "value" dimension, however, has a predefined `Number` "valueType".

    To avoid any problems we will define the dimensions explicitly.

    2) Explicitly define the internal data dimensions:

    Code:
    dimensions: {
         measure:       {valueType: String}, // default label is "Measure"
         date_produced: {valueType: Date,  isDiscrete: true, label: "Date produced"}, // default for Date value type is to be a continuous dimension
         product:       {valueType: String},
         value:         {valueType: Number}
    }

    3) Bind visual roles (visual space) to dimensions (data space):

    Code:
    visualRoles: {
         category: "date_produced, measure",
         series:   "product"
         value:    "value" // this is the default binding...
    }

    4) Turn the base axis into a composite (hierarchical axis):

    If you render the chart now, you'll see that each unique combination of values of <date_produced, measure> generated a different category with the label, for example: "3/14/14 ~ perc_of_weekly_demand".
    We want to show this as a table instead, so we do:

    Code:
    baseAxisComposite: true,
    baseAxisSize:      150  // choose appropriate size
    The composite axis implementation does not know how to automatically size itself.

    ----

    These are the most important steps.
    You may want to customize the date labels.
    You cannot easily provide the separation between clusters that you showed in the sample chart. Some have managed to create dummy categories to create those gaps...
    Last edited by duarte.leao; 04-10-2014 at 08:33 AM. Reason: typo

  3. #3
    Join Date
    Apr 2014
    Posts
    9

    Default

    @duarte.leao: Thanks very much for the help! How do I add that code to my CCC Bar Chart, though, in CDE? I see in the advanced properties where I can set crosstabmode to True, but I don't see properties for readers, visualRoles, etc. Is this something I add to the custom chart script property?

    Thanks!

  4. #4
    Join Date
    Mar 2012
    Posts
    645

    Default

    See this comment in another post: http://forums.pentaho.com/showthread...401#post352401

  5. #5
    Join Date
    Apr 2014
    Posts
    9

    Default

    Thanks, duarte! I will try this method out and hopefully it should work!

  6. #6
    Join Date
    Oct 2014
    Posts
    6

    Default

    Hello Duarte,

    I followed your jsfiddle example and it was straightforward to implement.

    However, data is relational (Series in Rows) and there are more than 2 "quarters"; thus the number of stacked bars for a particular category differs based on the input data. In your example (http://jsfiddle.net/duarteleao/v9DSc/), you used baseAxisGrid_visible extension point to show base axis grids once for every two stacked bars and that worked out well if there are two series. How can I have a base axis grid line for each category?

    Let's say there is additional quarter (Q3) in your data set.

    Thanks,

  7. #7
    Join Date
    Mar 2012
    Posts
    645

    Default

    Hi rey,

    I'm not sure I understood your requirement.
    I played a bit with the example and ended-up making another one, that is closer to the problem initially posed in this thread.
    Here it is: http://jsfiddle.net/duarteleao/2nxheka0/
    I commented out the extensionPoints and renderCallback (by prefixing option names with "_") so that it is easier to see what's going on under the hood.
    I also left a commented, alternative version of the series role, that changes the way colors are assigned quite a bit.
    In the example, there's more than two series... More categories (dates) could be easily added.
    Here's the result:

    Name:  ClusteredBars2.jpg
Views: 920
Size:  17.3 KB

    Could you please rephrase your question based on this example?

  8. #8
    Join Date
    Oct 2014
    Posts
    6

    Default

    This example is perfect: In my case, 14-03-2014 has demand, sales and forecast; while 21-03-2014 has demand and sales only.

    I would like to see a baseAxisGrid line after each date (category):A vertical line after each date(category) instead of each stacked bar (Series).
    Or else, can I add banded rows with background color using a second plot?

    Hope that gives more clarity..
    Last edited by vshrey; 09-10-2015 at 11:00 PM.

  9. #9
    Join Date
    Mar 2012
    Posts
    645

    Default

    Hi,

    I updated the data and the baseAxisGrid_visible extension point, in the example, so that it works like you asked for.

    Cheers

  10. #10
    Join Date
    Oct 2014
    Posts
    6

    Default

    Perfect! It worked.. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.