How can I get a percentage for a running-difference field?

[danasellers@udayton.edu]

hello all gurus,

I have the following report which is fairly straight forward the Diff column is a running-difference from year to year.  I'd like to be able to show the %diff of that column in a column next to it.  How can this be done?

Thanks for any advice/help!
Dana


INQUIRER
Academic Year        College/School             # of Students             Diff              %Diff
2006                     School of Business        386                 
2007                                                      400                          4                 
2008                                                      350                          -50
2009                                                      450                          100

PROSPECTS
Academic Year        College/School             # of Students             Diff              %Diff
2006                     School of Business        410                 
2007                                                      470                           60                 
2008                                                      350                          -120
2009                                                      650                          300

APPLICANTS
Academic Year        College/School             # of Students             Diff              %Diff
2006                     School of Business        700                 
2007                                                      600                          -100                 
2008                                                      750                          150
2009                                                      850                          100

[cschnu]

Take the applicants data set as an example

APPLICANTS
Academic Year        College/School             # of Students             Diff              %Diff
2006                     School of Business        700                
2007                                                      600                          -100                
2008                                                      750                          150
2009                                                      850                          100

Try this

Code Select Expand

Students/(Students-Diff)

The results should be this
2006    386   0   
2007    600   -100   0.857142857
2008    750   150   1.25
2009    850   100   1.133333333


if you are looking for the percent of increase/decrease then you will have to take the percent minus 100

Code Select Expand

(Students/(Students-Diff)) - 100

[danasellers@udayton.edu]

Thanks so much for getting back to me.  I had tried this in the past and it renders really odd percents - like in the 1,000's.  It seems pretty straight forward to me, but it appears Cognos has a different idea.  I'm wondering if it's because the Diff field is a 'running-difference'.

[cschnu]

You might try using the data item for the running-difference field rather that the source field from your Cognos package.  I believe this makes a difference in how Cognos generates the SQL.

[danasellers@udayton.edu]

I have the # of students defined as

count(distinct [ID] for [ACADEMIC_YEAR], [Admission Status Sort], [Admission Status], [COLLEGE_DESC])

which is pulling from the data item. 

The diff is defined as

running-difference([# of Students] for [Admission Status Sort], [Admission Status], [COLLEGE_DESC])

which is also the data item.   Is that what you mean?

[cschnu]

I have the # of students defined as

count(distinct [ID] for [ACADEMIC_YEAR], [Admission Status Sort], [Admission Status], [COLLEGE_DESC])

which is pulling from the data item. 

The diff is defined as

running-difference([# of Students] for [Admission Status Sort], [Admission Status], [COLLEGE_DESC])

which is also the data item.   Is that what you mean?

Right, so then when you calculate the percentage use the data item for both the count & running-difference. If you know SQL you can view the SQL Cognos generates and see what it is actually doing.

[danasellers@udayton.edu]

well, i do understand sql, however, i've never been too great at deciphring cognos/native sql - doesn't make sense to me.  when i check it it doesn't look like it's taking the query item - looks like it's still inturpreting it as the data source item.  i can post the sql if it will help to figure this running-diff thing, but it's ugly, as there are several case statements which makes the sql long.

[danasellers@udayton.edu]

I think i figured this out.  It's really conveluded but it seems to work.  thanks for all the input on this!