Date aggregation issue in a crosstab

[Rosanero4Ever]

Hi all,

I have a problem grouping date which are in rows of a crosstab.
Please, see the following image


As you can see, grouping date, the revenue result is not the sum of the revenue, as I expected. :-\
Does anybody help me to understand this behavior and how can I obtain the desidered sum?
Thanks very much in advance

[RandomHunter]

Hi,

could it be possible that you forgot to attach your image?

Hunter

[Rosanero4Ever]

Hi,
I posted an image preview linked to the image. Can't you see the image?

[RandomHunter]

Probably something to do with the firewall settings @ work. I'll check later today :)

Hunter

[Rosanero4Ever]

The full image is here:
http://postimg.org/image/9gralvy99/

[CognosPaul]

Is this a relational or dimensional source?

[Rosanero4Ever]

Relational (on Cognos Bi 10.2 FP1)

[Francis aka khayman]

use total to add the values

Aggregation using 'maximum' will take the largest value and use it as the revenue summary value

[Rosanero4Ever]

Hi,
thank you for your advice.
I edited the Revenue aggregation rule to "automatic" and the data element to total(...) but the result is not changed  :-\
I also tried the Calculated aggregation rule without success.
Have you any other idea?
Thank you very much

[Francis aka khayman]

did you try Total?

Hi,
thank you for your advice.
I edited the Revenue aggregation rule to "automatic" and the data element to total(...) but the result is not changed  :-\
I also tried the Calculated aggregation rule without success.
Have you any other idea?
Thank you very much

[Rosanero4Ever]

Yes, Total aggregation rule doen't work for my target  :-\

[Francis aka khayman]

Hmmm... ok I'm looking at your image more closely. That seems to be correct...

21.945,35 is the total for 28/01/10   Q4 2009


Your logic seems to be out of place. I think you want the total for Q4 2009 right? The display should be like this:

                                      Revenue         
                                                             
Q4 2009     22/01/10         3.128.368,07   ----> A
Q4 2009     28/01/10         21.945,35        ----> B
Total Q4 2009                   A + B

Yes, Total aggregation rule doen't work for my target  :-\

[Rosanero4Ever]

I would like group by maximum date for the Q4 2009. So the result shoud be the following

                                      Revenue         
Q4 2009     28/01/10     3150313,42        ----> A+B

[Francis aka khayman]

I see..


create a Data Item using this:

maximum([Date])


then replace [Date] with your Data Item ... lets call it [Max Date].
you might need to set the Aggregate Function of [Max Date] to 'None'


your crosstab will look like this

                                               [Revenue]
[Quarter]   [Max Date]             <#1234#>

*** or you can reverse [Quarter] and [Max Date]

[Rosanero4Ever]

Hi, I also tried this solution but unluckly it doesn't work because the total value reproduced is incorrect  :-[
Is it so hard obtain this simple aggregation result?  ???
Thank you very much for your time

[CognosPaul]

Crosstabs do some unexpected things in the background when working on relational databases. Are you using DQM or CQM?

First, can you confirm that the date is actually a date/datetime/timestamp and not a string? Max usually will work on a string, but may also cause unexpected results. Also, are your results a derived resultset from another query, or is it pulling straight from the db?

Try reproducing this in a list. If it works there, then we can work on getting it in an crosstab.

List should be:
Quarter - No aggregation
Date - Max aggregation
Revenue - Total aggregation

[Rosanero4Ever]

Are you using DQM or CQM?

DQM

First, can you confirm that the date is actually a date/datetime/timestamp and not a string? Max usually will work on a string, but may also cause unexpected results. Also, are your results a derived resultset from another query, or is it pulling straight from the db?

My date is a datetime and it is derived stright from the db

Try reproducing this in a list. If it works there, then we can work on getting it in an crosstab.

List should be:
Quarter - No aggregation
Date - Max aggregation
Revenue - Total aggregation

I reproduced  the query using a List in Query Studio and it works well.  ???
Thanks all

[CognosPaul]

I just tried it on the sample Go Sales Query with package on CQM and it works fine:

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/11.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Sales (query)']/model[@name='model']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Quarter" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Time].[Quarter]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Date" rollupAggregate="none" aggregate="maximum"><expression>[Sales (query)].[Time].[Date]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="4" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Revenue" aggregate="total"><expression>[Sales (query)].[Sales].[Revenue]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="2" output="no"/><XMLAttribute name="RS_dataUsage" value="fact" output="no"/></XMLAttributes></dataItem><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Time].[Year]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem></selection>
</query>
</queries>
<layouts>
<layout>
<reportPages>
<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
<contents>
<crosstab refQuery="Query1" horizontalPagination="true" name="Crosstab1">
<crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style><contents/></crosstabCorner>


<noDataHandler>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue>No Data Available</staticValue>
</dataSource>
<style>
<CSS value="padding:10px 18px;"/>
</style>
</textItem>
</contents>
</block>
</contents>
</noDataHandler>
<style>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
<CSS value="border-collapse:collapse"/>
</style>
<crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Quarter" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Date" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns></crosstab>
</contents>
</pageBody>


</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="false" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2011-06-09T13:52:15.420Z" output="no"/></XMLAttributes></report>

My guess is that the DQM is effectively turning this into a cube. It's generating the data, finding the last date member, and returning that date's associated data.

[Rosanero4Ever]

But I'm using a relational view.
Cognos turn into a cube represantion  with  DMR  also using CQM

[Francis aka khayman]

I don't think I am using DQM as well and it was working fine.

How about if you put your data items in a query including your maximum computations, etc... then get a second query which source is your first query. Take only the data items you need and use this as a source of your crosstab?

Or you can pre compute your dimensions in 1 query, your numbers in a second query, join them in a third query to use for your crosstab?

The idea is to 'convert' your data to simple relational/non DQM mode?

Thats all the bad ideas I have for now :(

[Rosanero4Ever]

Hi,

I'm thinking just this work around, i.e. create two queries and join them.
I opened a PMR at IBM support; I will let you know about other solutions.
Thank you very much for your time.

[Rosanero4Ever]

Hi,

I opened an IBM call and the described behavior was classified as defect. So, I'll wait the APAR number.
In the meantime I solved using a join between two queries.
I hope this will help other developers.
Thanks all for advices.