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

Percent of totals within a group problem

Started by dxwallace, 08 Jun 2007 03:15:40 PM

Previous topic - Next topic

dxwallace

Can someone help me a novice with how to do this?

I have a list object grouped where my data will give three separate groups based on the data. I am trying to have a column that represents the percentage of another column's numerical value relative to just the group, not the percentage of the all three groups. Let me try to illustrate below.

For each line below, the percentage should be 50%, because the data value is 50% of the group as opposed yo 16.67% of everything which I am currently getting because my percentage field is
percent(valuefld) * 100. How do I do this at the group level?
                   
                  valuefld       percent     
Group  a          50          16.67      [should be 50%)
Group  a          50          16.67

Group  b          50          16.67
Group  b          50          16.67

Group  c           50          16.67
Group  c           50          16.67
                      300
                           

MDXpressor

You could try creating a calc to summarize the data to the group level, then base your percentage on that column.

[Group Sum]:
total([valuefld] for [group])

[Percentage]:
[valuefld]/[Group Sum]
No, a proof is a proof. What kind of a proof? It's a proof. A proof is a proof, and when you have a good proof, it's because it's proven.

-Jean Chretien

dxwallace

I took the "chumps" way out on this one.  I basically separated the three groups into three different queries on 3 different pages, thus allowing each group summary to total independently as a separate page query.

MDXpressor

No such thing as the 'Chumps' way as long as you didn't underdeliver to the client.  Creativity provides many paths to the same destination.

No, a proof is a proof. What kind of a proof? It's a proof. A proof is a proof, and when you have a good proof, it's because it's proven.

-Jean Chretien

Desperado

I think u cannnot use total and for in the group sum calc....
you have to use aggregate if you want to use "for "
[Group Sum]:
aggregate([valuefld] for [group])

dxwallace

I hear you MDXpressor, there are several ways to skin a cat, I just wnat to make sure I am learning as I go all the ways Cognos can do something, because in the future I might not be able to separate the three gropus into three separate queries and I perfer to know as much as i can


MDXpressor

Quote from: Desperado on 21 Jun 2007 08:30:38 AM
I think u cannnot use total and for in the group sum calc....
you have to use aggregate if you want to use "for "
[Group Sum]:
aggregate([valuefld] for [group])


Not sure where you got this, but I use Total...for quite frequently, so I am pretty sure you can.
No, a proof is a proof. What kind of a proof? It's a proof. A proof is a proof, and when you have a good proof, it's because it's proven.

-Jean Chretien