No Average in Data Cubes? [message #365139] |
Sat, 13 September 2008 14:12 |
Steven Messages: 32 Registered: July 2009 |
Member |
|
|
When making a data cube, there are many missing aggregate functions. Any
reason why? I can't calculate the average or do anything the specifies
a group (there is no "Aggregate on" I assume thats why I cant use the
functions that require it).
I don't know If I'm being clear so let me give an example:
Lets say I have a data cube with dimensions Car/Make and Date, and
measure Sales(Sum). I can make across tab like this:
| | April |
| | M | T | W | T | F | S | S |
--------------------------------------
|ford | 3 | 2 | 1 | 0 | 4 | 3 | 2 |
|honda | 5 | 6 | 7 | 2 | 5 | 1 | 0 |
|chevy | 4 | 6 | 0 | 3 | 7 | 1 | 0 |
--------------------------------------
|total |12 |14 | 8 | 5 |16 | 5 | 2 |
However, how do I make the cross tab with the average sales per day for
all months? It looks like I have to make the query calculate it first,
then use the value with a last, first, min, or max aggregation. I must
be missing something, as most other aggregations and groupings are so
easy in Birt.
Any pointers?
Thanks,
Steven
|
|
|
|
|
(no subject) [message #718204 is a reply to message #718075] |
Tue, 23 August 2011 14:56 |
|
Johannes,
Any chance you could write a report with the sample db or a scripted
dataset that sets some sample values that would show the issue? That
way we could test it.
Jason
On 8/23/2011 3:28 AM, Johannes.Koshy wrote:
> Hi, i have a similar problem where i would like to use AVG instead of
> the offered ones.
> My Data would look like
>
> Name|Date|Value
> and My Data Cube would Group by Name, Group by Day of Week and then
> summarize the value by max.
> For the summary of the value i would love, average instead of max.
> How would i achive this?
> I use the Data for an Pie Chart with Category Axis, Name, yAxis Val and
> opt-Y-Achis Grouping the Day of Week.
>
> I tried to make to summaries(measures), sum and count, but didnt know
> how to use them to define a third, AVG.
>
>
>
|
|
|
|
Re: (no subject) [message #718520 is a reply to message #718384] |
Wed, 24 August 2011 15:15 |
|
Johannes
I was able to modify the attached report to calculate an average with a
derived measure. First add another measure to the cube that does a
count. Then add a derived measure to the xtab that divides the sum
measure by the count measure. See attached.
Jason
On 8/24/2011 3:39 AM, Johannes.Koshy wrote:
> <?xml version="1.0" encoding="UTF-8"?>
> <report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.21"
> id="1">
> <property name="comments">Copyright (c) 2007 <<Your Company Name
> here>></property>
> <property name="createdBy">Eclipse BIRT Designer Version 2.6.0.v20100531
> Build <2.6.0.v20100609-1613></property>
> <html-property name="description">Creates a blank report with no
> predefined content.</html-property>
> <property name="units">in</property>
> <property name="iconFile">/templates/blank_report.gif</property>
> <property name="layoutPreference">auto layout</property>
> <data-sources>
> <script-data-source name="Data Source" id="5"/>
> </data-sources>
> <data-sets>
> <script-data-set name="Data Set" id="6">
> <list-property name="resultSetHints">
> <structure>
> <property name="position">1</property>
> <property name="name">category</property>
> <property name="dataType">string</property>
> </structure>
> <structure>
> <property name="position">2</property>
> <property name="name">date</property>
> <property name="dataType">date-time</property>
> </structure>
> <structure>
> <property name="position">3</property>
> <property name="name">series2</property>
> <property name="dataType">float</property>
> </structure>
> </list-property>
> <list-property name="columnHints">
> <structure>
> <property name="columnName">category</property>
> </structure>
> <structure>
> <property name="columnName">date</property>
> </structure>
> <structure>
> <property name="columnName">series2</property>
> </structure>
> </list-property>
> <structure name="cachedMetaData">
> <list-property name="resultSet">
> <structure>
> <property name="position">1</property>
> <property name="name">category</property>
> <property name="dataType">string</property>
> </structure>
> <structure>
> <property name="position">2</property>
> <property name="name">date</property>
> <property name="dataType">date-time</property>
> </structure>
> <structure>
> <property name="position">3</property>
> <property name="name">series2</property>
> <property name="dataType">float</property>
> </structure>
> </list-property>
> </structure>
> <property name="dataSource">Data Source</property>
> <method name="open"><![CDATA[i = 0;
>
> sourcedata = new Array( new Array(3),
> new Array(3),
> new Array(3),
> new Array(3),
> new Array(3),
> new Array(3));
> sourcedata[0][0] = "Item1";
> sourcedata[0][1] = new Date();
> sourcedata[0][2] = 10;
>
> sourcedata[1][0] = "Item2";
> sourcedata[1][1] = new Date();
> sourcedata[1][2] = 65;
>
> sourcedata[2][0] = "Item2";
> sourcedata[2][1] = new Date();
> sourcedata[2][2] = 25;
>
> sourcedata[3][0] = "Item1";
> sourcedata[3][1] = new Date();
> sourcedata[3][2] = 20;]]></method>
> <method name="fetch"><![CDATA[if ( i < 4 )
> {
> row["category"] = sourcedata[i][0];
> row["date"] = sourcedata[i][1];
> row["series2"]= sourcedata[i][2];
> i++;
> return true;
> }
> return false;]]></method>
> </script-data-set>
> </data-sets>
> <cubes>
> <tabular-cube name="Data Cube" id="54">
> <property name="dimensions">
> <tabular-dimension name="Group" id="55">
> <property name="defaultHierarchy">NewTabularHierarchy</property>
> <property name="hierarchies">
> <tabular-hierarchy name="NewTabularHierarchy" id="56">
> <property name="levels">
> <tabular-level name="category" id="57">
> <property name="dataType">string</property>
> <property name="columnName">category</property>
> </tabular-level>
> </property>
> </tabular-hierarchy>
> </property>
> </tabular-dimension>
> <tabular-dimension name="Group1" id="58">
> <property name="isTimeType">true</property>
> <property name="defaultHierarchy">NewTabularHierarchy1</property>
> <property name="hierarchies">
> <tabular-hierarchy name="NewTabularHierarchy1" id="59">
> <property name="levels">
> <tabular-level name="Day Of Week" id="60">
> <property name="dataType">integer</property>
> <property name="dateTimeLevelType">day-of-week</property>
> <list-property name="attributes">
> <structure>
> <property name="name">DateTime</property>
> <property name="dataType">date-time</property>
> </structure>
> </list-property>
> <property name="columnName">date</property>
> </tabular-level>
> </property>
> </tabular-hierarchy>
> </property>
> </tabular-dimension>
> </property>
> <property name="measureGroups">
> <tabular-measure-group name="Summary Field" id="61">
> <property name="measures">
> <tabular-measure name="series2" id="62">
> <property name="function">sum</property>
> <expression name="measureExpression"
> type="javascript">dataSetRow["series2"]</expression>
> <property name="dataType">float</property>
> </tabular-measure>
> </property>
> </tabular-measure-group>
> </property>
> <property name="dataSet">Data Set</property>
> </tabular-cube>
> </cubes>
> <page-setup>
> <simple-master-page name="Simple MasterPage" id="2">
> <property name="topMargin">1in</property>
> <property name="leftMargin">1.25in</property>
> <property name="bottomMargin">1in</property>
> <property name="rightMargin">1.25in</property>
> </simple-master-page>
> </page-setup>
> <body>
> <extended-item extensionName="Crosstab" extensionVersion="2.5.0" id="63">
> <property name="cube">Data Cube</property>
> <property name="measures">
> <extended-item extensionName="MeasureView" id="75">
> <property name="measure">series2</property>
> <property name="detail">
> <extended-item extensionName="AggregationCell" id="76">
> <property name="aggregationOnRow">Group/category</property>
> <property name="aggregationOnColumn">Group1/Day Of Week</property>
> <property name="content">
> <data id="77">
> <property name="resultSetColumn">series2_Group/category_Group1/Day Of
> Week</property>
> </data>
> </property>
> </extended-item>
> </property>
> <property name="header">
> <extended-item extensionName="CrosstabCell" id="78">
> <property name="content">
> <label id="79">
> <text-property name="text">series2</text-property>
> </label>
> </property>
> </extended-item>
> </property>
> </extended-item>
> </property>
> <property name="rows">
> <extended-item extensionName="CrosstabView" id="65">
> <property name="views">
> <extended-item extensionName="DimensionView" id="66">
> <property name="dimension">Group</property>
> <property name="levels">
> <extended-item extensionName="LevelView" name="NewLevel View" id="67">
> <property name="level">Group/category</property>
> <property name="member">
> <extended-item extensionName="CrosstabCell" id="68">
> <property name="content">
> <data name="category" id="69">
> <property name="resultSetColumn">category</property>
> </data>
> </property>
> </extended-item>
> </property>
> </extended-item>
> </property>
> </extended-item>
> </property>
> </extended-item>
> </property>
> <property name="columns">
> <extended-item extensionName="CrosstabView" id="70">
> <property name="views">
> <extended-item extensionName="DimensionView" id="71">
> <property name="dimension">Group1</property>
> <property name="levels">
> <extended-item extensionName="LevelView" name="NewLevel View1" id="72">
> <property name="level">Group1/Day Of Week</property>
> <property name="member">
> <extended-item extensionName="CrosstabCell" id="73">
> <property name="content">
> <data name="Day Of Week" id="74">
> <property name="resultSetColumn">Day Of Week</property>
> </data>
> </property>
> </extended-item>
> </property>
> </extended-item>
> </property>
> </extended-item>
> </property>
> </extended-item>
> </property>
> <property name="header">
> <extended-item extensionName="CrosstabCell" id="64"/>
> </property>
> <list-property name="boundDataColumns">
> <structure>
> <property name="name">category</property>
> <expression name="expression">dimension["Group"]["category"]</expression>
> <property name="dataType">string</property>
> </structure>
> <structure>
> <property name="name">Day Of Week</property>
> <expression name="expression">dimension["Group1"]["Day Of
> Week"]</expression>
> <property name="dataType">integer</property>
> </structure>
> <structure>
> <property name="name">series2_Group/category_Group1/Day Of Week</property>
> <expression name="expression">measure["series2"]</expression>
> <property name="dataType">float</property>
> <simple-property-list name="aggregateOn">
> <value>Group/category</value>
> <value>Group1/Day Of Week</value>
> </simple-property-list>
> <property name="aggregateFunction">SUM</property>
> </structure>
> </list-property>
> </extended-item>
> </body>
> </report>
>
> I Hope this is enough, where there is <property
> name="aggregateFunction">SUM</property> i want to achieve an Average.
-
Attachment: avgxtab.zip
(Size: 2.24KB, Downloaded 282 times)
|
|
|
|
|
Re: (no subject) [message #718893 is a reply to message #718788] |
Thu, 25 August 2011 15:02 |
|
It may be easier in this case to go back to the original dataset and
bypass the cube. See attached example.
Jason
On 8/25/2011 6:44 AM, Johannes.Koshy wrote:
> Another Question, when i use the data["Group1/Day Of Week"] field as
> Optional Grouping only the Number of the Week is shown.
> Even though i have set it to show the String. For an CrossTab it shows
> the String representation.
>
> Or more general, when i use Optional Grouping how do i format it.
> I Mean when i have a Pie Chart for every day in the Week via the
> Optional Grouping i would love to have the String representation of
> those days as Series Labels, until
> know all i got was a full date or similar.
-
Attachment: avgxtab.zip
(Size: 7.36KB, Downloaded 244 times)
|
|
|
|
Re: (no subject) [message #719263 is a reply to message #719105] |
Fri, 26 August 2011 14:23 |
|
Johannes,
There is a sort icon beside the category and optional grouping settings
on the second tab of the chart wizard that allows you to group on a unit
type. For example with dates you can do things like year, month,
quarter, week etc. I modified the example to change the scripted
dataset to have two months and then I changed the grouping for the
optional grouping field to be months.
Jason
On 8/26/2011 1:12 AM, Johannes.Koshy wrote:
> This would not yield the same result i guess :/, cause
> in the data cube you have grouping options like Week of the Month etc.
> When you have a timeframe of one Year but wanted an average for each day
> in a week,
> you would get it with Day of the Week in the data cube, but not with the
> Chart by using its
> grouping options.
-
Attachment: avgxtab.zip
(Size: 7.45KB, Downloaded 255 times)
|
|
|
Powered by
FUDForum. Page generated in 0.04151 seconds