Calculating percentages in a table

Question

Post reply

Calculating percentages in a table

one_too_many

SSC Enthusiast

Points: 179
More actions
April 16, 2008 at 1:07 pm

#186560

I have read through lots of posts on this already but cannot find this bit covered.
I have my data group at the top level by an type.. 'a,b,c,d,e'. I need to show a row 'f' which is the product of (sum(c)/sum(e)) *100.
week1 week2 week3
A 1358 1306 1232
B -46 -77 -239
C 73 31 129
D 9 0 0
E 4 6 11
F 5.48 19.35 8.53
This row is the top level of the grouping and so I need to cascade this down into the lower
groups but I guess if I can get the top level working it would just be the same.
Many thanks
M

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Just to be clear: Are you doing this calculation in a query in the database, or in some reporting application?

If it's a query in the database, I can probably help with that.

In your formula, would "sum(c)" be 73+31+129, as those are the values in row c, or would it be something else?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 2

And, just a note... all the data should be stored vertically instead of horizontally... it should only be pivoted or cross-tabbed for display purposes... would make your calculations a whole lot easier.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

one_too_many SSC Enthusiast Points: 179 More actions · Answer 3

Hi,

The data is from an sql table stored at the lowest level but the % value I need must be produced in the report as it is used at various levels in 3 reports which all use the same data souce by report different arrgegations of it.

The key to it is that at the level it is show I need to take a value and divide it by another, these values are only shown at one level. I have looked at inscope but that just seems to check the row/column your at but not bring back the value.

I need to get the value for the row above (previous) and for the Nth row above.

????

Thanks

M.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

You have to use the grouping scope parameters.

Assuming your grouping looks like:

GroupA

Group B

Group C

Detail

/Group C

/Group B

Group B

Group C

Detail

/Group C

/Group B

/GroupA

..etc...

You can refer to aggregates from the OUTER groups in the INNER groups. Meaning - in what I just showed, you can refer to the A level aggregate in B or C, but you can't refer to C from within A.

Using that, you can (within the lower level) add a new column, and a new textbox, so as to put in:

=(sum(fields!week1.value,"GroupC")/sum(fields!week1.value,"GroupB"))

and format that as 0.00% - it will handle moving the decimal around....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 5

martin scott (4/16/2008)
The data is from an sql table stored at the lowest level ...

Then, post the CREATE TABLE statement and some example data as outlined in the URL in my signature line... it's the only way we can stop guessing...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

one_too_many SSC Enthusiast Points: 179 More actions · Answer 6

The problem is I need to referrer to a line within the same grouping.

The data is headcount data from the result of a pivot from which I get 5 data streams;

budget

actual

starters

leavers

budget vs actual.

This is for many departments but lets just say 2 for now and 54 job roles. This is pushed out into columns for each week of the year.

In the report I need to group the data by

DataStream

Department

Job Role

and need to show the vaule of leaver % of actual in the same grouping as the 5 data streams.

I tried to create this value in the data set but it doesn't stack up when the group is sumed to provide the values at the required level.

Here is a create table to give some idea as to the data structrue. There are 50+ departments, 4 job roles and a column for each week of the year.

create table t_data as

DataStreamID int,

DataStream varchar(10),

DepartmentName varchar(20),

JobRole varChar(10),

Week1 int,

Week2 int,

Week3 int,

Week4 int,

Week5 int

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 7

Sorry Martin, I'm going to have to bow out... I don't know much about the reporting tools.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

martin scott (4/22/2008)
The problem is I need to referrer to a line within the same grouping.
The data is headcount data from the result of a pivot from which I get 5 data streams;
budget
actual
starters
leavers
budget vs actual.
This is for many departments but lets just say 2 for now and 54 job roles. This is pushed out into columns for each week of the year.
In the report I need to group the data by
DataStream
Department
Job Role
and need to show the vaule of leaver % of actual in the same grouping as the 5 data streams.
I tried to create this value in the data set but it doesn't stack up when the group is sumed to provide the values at the required level.
Here is a create table to give some idea as to the data structrue. There are 50+ departments, 4 job roles and a column for each week of the year.
create table t_data as
DataStreamID int,
DataStream varchar(10),
DepartmentName varchar(20),
JobRole varChar(10),
Week1 int,
Week2 int,
Week3 int,
Week4 int,
Week5 int

Have you tried what I showed you above?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

one_too_many SSC Enthusiast Points: 179 More actions · Answer 9

Hi Matt,

Thanks for coming back to me. Yes I have tried;

=(sum(fields!week1.value,"GroupC")/sum(fields!week1.value,"GroupB"))

But the value I need is from the same group. May be previous would be the way to go. I need to look at the previous row to get one value but a good few rows up to get the other.

Any help would be great as I'm now looking to have multiple reports just to show the % values at the different levels this report groups at.

KRs

Mart

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 10

Try the following:

[Code]

Select DataStreamID, DataStream, DepartmentName, JobRole, Week1, Week2, Week3, Week4, Week5

FROM t_data

Union

Select 99, 'Leaver %', DepartmentName, JobRole, L.Week1/A.Week1, L.Week2/A.Week2, L.Week3/A.Week3, L.Week4/A.Week4, L.Week5/A.Week5

From t_data L

INNER JOIN t_data A ON L.JobRole = A.JobRole

and L.DepartmentName = A.DepartmentName

WHERE L.DataStream = 'leavers'

and A.DataStream = 'actual'

ORDER BY 1

[/Code]

As an alternative, I'd suggest you consider putting the "data streams" in as facts and the periods in as primary key. Then, the calcualtion is quite straight forward ("leavers" / "actual") , and the data is more easily consumed by BI tools (like Analysis Services).

one_too_many SSC Enthusiast Points: 179 More actions · Answer 11

That's a a thought I could try to twist the data the other way. After a long time staring at the same data/reports I've gone blind to other avenues.

Many thanks.

Mart