If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

YTD calculation depending on the metrics

Started by cham, 14 Sep 2011 04:11:26 PM

Previous topic - Next topic

cham

Hi Gurus,

I need some help for YTD calculation(Total or Average) depending on the metrics. But it should show under one column(YTD) in the crosstab.

Suppose my crosstab looks like :

               Jan 2011   Feb 2011  Mar 2011  YTD

Metric A      1                 1            1               3  (total)             
Metric B      2                 2            2               2  (average)
Metric C      3                 1            2               6  (total)     

.
.
Metric N     3                  5            1               3(average)

So, under YTD column it should show either TOTAL or AVERAGE(for time) depending on the metrics. For few metrics it should be total and for few of them it should be Average.

So, My question is how to show total or average calculation under the same column(YTD) in the crosstab.

Please help me in this regard.

thank you.

cham


CognosPaul


PRIT AMRIT

Have you tried to manipulate the cognos generated YTD total column? How about writing a case or if/else statement within it?

case
when [Metric] ='Metric A ' then Total([Measure])
when [Metric] ='Metric B ' then average([Measure])
----
-----
end

Does it helps?

cham


cham

Hi Prit,

Can you please explain this in detail. I am not getting how to get [Metric] in the above mentioned CASE statement. Please explain it in detail.

CognosPaul

You can control the aggregation displayed with clever use of style variables. First, what logic do you use to determine which metric displays which form of aggregation?

Let's assume you have an member attribute for it. So drag that attribute to the query, and attach it to the properties of your member node. Create a new string variable called "Agg Type". Values: Total, Avg. The variable expression should be something like:

[Query 1].[Agg Type Attribute] or case when [Query 1].[Agg Type Attribute] = 1 then 'Total' else 'Avg' end

Now the clever bit. Drag in both a total and an avg fields to the crosstab.

So it should look something like:

Corner   | Measure | Total | AVG
---------+---------+-------+-----
Metric A |  123    |  123  |  123
Metric B |  123    |  123  |  123
Metric C |  123    |  123  |  123
Metric D |  123    |  123  |  123


Right click on the Total Node and select member fact cells. Set the style variable to "Agg Type" and check off Avg. When it's AVG then the box type should be none. Use the same idea for the AVG column.

Set the AVG column header to box type none, and rename Total to something more generic.

That should work now.

cham

#7
Thanks. I will try this.

kalyanapu

I agree with Paul Suggestion.
It works well.

-
Srinivas Kalyanapu