Dynamic aggregation required

[hespora]

Hi there,


I'm trying to design an interactive report in order to analyze selectable measures at selectable levels of granularity.
Included in that is a limitation to show a max of top 10 records plus an "other" record. a simple mockup from the go data warehouse (query) package is attached.

Everything works fine, except displaying measures that should calculate rather than total in aggregation. In the example attached, select measure "GP%" (which in the raw data query is simply profit/revenue), and a category that will yield more than 11 results, like product type. all the measure values from records with a rank >= 11 are added up, resulting in a profitability measure of 416%. yay. :/

I'm ripping out hair over this; I cannot think of a decent approach to get the breakdown measure to dynamically use a different method of aggregation (and I realize I'm gonna have to do this in the definition; not via the aggregate function properties). Anyone got ideas?

[Lynn]

Hi there,


I'm trying to design an interactive report in order to analyze selectable measures at selectable levels of granularity.
Included in that is a limitation to show a max of top 10 records plus an "other" record. a simple mockup from the go data warehouse (query) package is attached.

Everything works fine, except displaying measures that should calculate rather than total in aggregation. In the example attached, select measure "GP%" (which in the raw data query is simply profit/revenue), and a category that will yield more than 11 results, like product type. all the measure values from records with a rank >= 11 are added up, resulting in a profitability measure of 416%. yay. :/

I'm ripping out hair over this; I cannot think of a decent approach to get the breakdown measure to dynamically use a different method of aggregation (and I realize I'm gonna have to do this in the definition; not via the aggregate function properties). Anyone got ideas?

Attached is a revised version of the report. I did this in Analytics 11.0.7 but I see you posted in the Cognos10 forum.  I think you can hack the header in the XML to open it in 10.

After my changes it gives me 0.3796 for the "Others" group in Product type which I believe is the correct figure for gross profit divided by revenue. I find that calculations work best when done at the lower level reference queries rather than at the start of the process for exactly the reasons you encountered. The basic approach is to push the base query elements into the reference query and do the computation there. The main query is done to figure out the ranking and set the item name as Others or the actual item name based on the rank.

Here is how I made the changes:

Created a "rank" calculation in the main query with aggregate property set to calculated:
case
  when rank( [Breakdown Measure] ) >= 11 then 11
else rank( [Breakdown Measure] )
end

Created a "Breakdown Item ranked" calculation in the main query with aggregate property set to default:
case [rank]
when 11 then 'Others'
else [Breakdown Item]
end

Modified the ranked query to have these items:
Revenue: [main query].[Revenue] <-- set to total
Gross Profit: [main query].[Gross profit]  <-- set to total
GP%: [Gross profit] / [Revenue]  <-- set to calculated
Breakdown Measure: #prompt( 'pBreakdown_Measure' , 'token' )#  <-- set to default
rank: [main query].[rank]  <-- set to calculated
Breakdown Item: [main query].[Breakdown Item ranked]  <-- set to none

Modified the chart to also use ranked query with master detail set as original as it filters to provide the correct value anyway so I didn't see the need for the additional query reference.

Hope this solves it or gives you inspiration on other approaches.

[hespora]

The basic approach is to push the base query elements into the reference query and do the computation there.

Oh.... yeah, that makes perfect sense. I'll try that and slap my forehead a couple of times in the process. Thank you very much! :)

[Lynn]

Happy to help!