Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Chart aggregates data resulting in wrong graph

  1. #1
    Join Date
    May 2010
    Posts
    13

    Default Chart aggregates data resulting in wrong graph

    I want to draw a bar graph for the following MDX query which should
    have x axis labled with each week of each month, for example January
    Week 1, January Week 2,......, December Week 4.

    MDX query:

    Code:
    select NON EMPTY {[State].[All States].Children} ON COLUMNS, NON EMPTY 
    {[Time].[Week].Members} ON ROWS from [gpBillFact] where [Item].[GPMP]
    MDX output:

    Code:
    Axis #0:
    {[Item].[All Items].[GPMP]}
    Axis #1:
    {[State].[All States].[SA]}
    {[State].[All States].[VIC]}
    {[State].[All States].[WA]}
    Axis #2:
    {[Time].[All Times].[2010].[April    ].[Week 1]}
    {[Time].[All Times].[2010].[April    ].[Week 2]}
    {[Time].[All Times].[2010].[April    ].[Week 3]}
    {[Time].[All Times].[2010].[April    ].[Week 4]}
    {[Time].[All Times].[2010].[April    ].[Week 5]}
    {[Time].[All Times].[2010].[February ].[Week 1]}
    {[Time].[All Times].[2010].[February ].[Week 2]}
    {[Time].[All Times].[2010].[February ].[Week 3]}
    {[Time].[All Times].[2010].[February ].[Week 4]}
    {[Time].[All Times].[2010].[January  ].[Week 1]}
    {[Time].[All Times].[2010].[January  ].[Week 2]}
    {[Time].[All Times].[2010].[January  ].[Week 3]}
    {[Time].[All Times].[2010].[June     ].[Week 1]}
    {[Time].[All Times].[2010].[March    ].[Week 1]}
    {[Time].[All Times].[2010].[March    ].[Week 2]}
    {[Time].[All Times].[2010].[March    ].[Week 3]}
    {[Time].[All Times].[2010].[March    ].[Week 4]}
    {[Time].[All Times].[2010].[March    ].[Week 5]}
    {[Time].[All Times].[2010].[May      ].[Week 1]}
    {[Time].[All Times].[2010].[May      ].[Week 2]}
    {[Time].[All Times].[2010].[May      ].[Week 3]}
    {[Time].[All Times].[2010].[May      ].[Week 4]}
    {[Time].[All Times].[2010].[May      ].[Week 5]}
    Row #0: 
    Row #0: 5
    Row #0: 
    Row #1: 
    Row #1: 8
    Row #1: 4
    Row #2: 
    Row #2: 5
    Row #2: 4
    Row #3: 2
    Row #3: 8
    Row #3: 2
    Row #4: 
    Row #4: 2
    Row #4: 
    Row #5: 
    Row #5: 6
    Row #5: 1
    Row #6: 7
    Row #6: 7
    Row #6: 1
    Row #7: 
    Row #7: 7
    Row #7: 
    Row #8: 1
    Row #8: 3
    Row #8: 20
    Row #9: 
    Row #9: 1
    Row #9: 
    Row #10: 
    Row #10: 5
    Row #10: 
    Row #11: 
    Row #11: 1
    Row #11: 4
    Row #12: 1
    Row #12: 10
    Row #12: 2
    Row #13: 2
    Row #13: 12
    Row #13: 1
    Row #14: 
    Row #14: 12
    Row #14: 7
    Row #15: 
    Row #15: 14
    Row #15: 3
    Row #16: 
    Row #16: 6
    Row #16: 1
    Row #17: 
    Row #17: 2
    Row #17: 3
    Row #18: 2
    Row #18: 12
    Row #18: 
    Row #19: 
    Row #19: 15
    Row #19: 
    Row #20: 
    Row #20: 8
    Row #20: 3
    Row #21: 2
    Row #21: 11
    Row #21: 5
    Row #22: 
    Row #22: 16
    Row #22:

    The graph I get with the above MDX aggregates for each week of all
    months.

    Example: SA, Week1 =5 (March Week 1 =2 + May Week 1 = 2 + June Week 1
    =1 )
    But what I want is to get each week seperate for each month.

    Since MDX gives the correct output I cannot figure out where the
    aggregation happens when drawing the graph. Could you please help me
    with this.

    Thanks a lot.

    regards,
    Kumari

  2. #2
    Join Date
    May 2010
    Posts
    13

    Default

    I'm still struggling with this issue and thought of asking for help again.

    I have following MDX query:

    select {[State].[All States].[VIC]} ON ROWS, NON EMPTY {[Week].Members} ON COLUMNS from [gpBillFact] where ([Item].[GPMP])

    This basically gives me the measure corresponds to [year][month][week]. So in summary the results are like this:
    [2010].[April ].[Week 1] = 5
    2010].[April ].[Week 2] = 8
    2010].[April ].[Week 3] = 5
    2010].[April ].[Week 4] = 8
    2010].[April ].[Week 5] = 2
    [2010].[February ].[Week 1] = 6
    [2010].[February ].[Week 2] = 7
    [2010].[February ].[Week 3] = 7
    [2010].[February ].[Week 4] = 3
    [2010].[January ].[Week 1] = 1
    [2010].[January ].[Week 2] = 5
    [2010].[January ].[Week 3] = 1
    [2010].[June ].[Week 1] = 10
    [2010].[March ].[Week 1] = 12
    [2010].[March ].[Week 2] = 12
    [2010].[March ].[Week 3] = 14
    [2010].[March ].[Week 4] = 6
    [2010].[March ].[Week 5] = 2
    [2010].[May ].[Week 1] = 12
    [2010].[May ].[Week 2] = 15
    [2010].[May ].[Week 3] = 8
    [2010].[May ].[Week 4] = 11
    [2010].[May ].[Week 5] = 16

    I want this information on a chart. I use CDF chart as follows

    gpmpStateGenerationChart =
    {
    name: "gpmpStateGenerationChart",
    type: "jFreeChartComponent",
    listeners: ["state","cType"],
    chartDefinition: gpmpStateGenerationChartDefinition,
    htmlObject: "gpmpStateGenerationChart_object",
    executeAtStart: true
    }

    /// ------- Chart Definitions ------- ///
    gpmpStateGenerationChartDefinition =
    {
    width: 250,
    height: 350,
    chartType: function(){return cType},
    datasetType: "CategoryDataset",
    is3d: false,
    byRow: true,
    isStacked: false,
    includeLegend: true,
    domainLabelRotation: .9,
    backgroundColor: "#FFFFFF",
    plotbgcolor:"#edf7d0",
    title: function() {
    // var selectedOrganisation= organisation=="null"?"All Organisations": "Organisation: "+organisation;
    var selectedState= state=="null"?".[All States]": state;
    var title="Number of GPMP by Selected State: " + selectedState;
    return title;
    },
    orientation: 'vertical',
    queryType: 'mdx',
    catalog: 'solution:CDMSCube/gpbillwithpostcode.xml',
    jndi: "CDMSDataMart",
    query: function(){
    var query = "select {[State].[All States].[VIC]} ON ROWS, NON EMPTY {[Week].Members} ON COLUMNS from [gpBillFact] where ([Item].[GPMP])";
    return query;
    }
    }



    However, the bar graph I'm getting gives aggregates values for week1, week2, week3, week4 and week5 without separating them for year, month, week. I get a graph which reads week 1 = 46, week 2 = 47, week 3 = 35, week 4 = 28 and week 5 = 20.

    Could you please help me to get this graph correctly?

    Thanks a lot.

    regards,
    Kumari

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.