I do not get the Aggregate value when I give the foll formula

[junction123]

I have a report that needs to aggregate two columns based on every quarter.

The report final output has the foll fields And for categorizing based on All, Historic and New we have the
ExecuteToCoverDiff and ResponseCount for all three categories.
TradeMonthName
TradeYear
ExecuteToCoverDiff
ResponseCount
ExecuteToCoverDiff
ResponseCount
ExecuteToCoverDiff
ResponseCount
TradeMonth

What is wrong in my formula to return 0? I grouped by ResponseCount and have the aggregated field in the group.


IF {rptAvgSpreadToCoverRatio.TradeMonth} = 'January'
AND {rptAvgSpreadToCoverRatio.TradeMonth} = 'February'
AND {rptAvgSpreadToCoverRatio.TradeMonth} = 'March'
THEN Average({rptAvgSpreadToCoverRatio.ExecuteToCoverDiff})

I have months from January...December.

The fields in question are ExecuteToCoverDiff and ResponseCount and the report output should appear as
Cover ResponseCount
January 25.92 3.86
February 24.39 3.76
March 25.21 3.77
2009 Q1 25.16 3.80

April 19.27 3.88
May 18.26 3.94
June 13.87 4.10
2009 Q2 16.78 3.99


thanks in advance for your help

 

[lbass]

You either need to change the 'and's to 'or's or use "in" as in:

If {rptAvgSpreadToCoverRatio.TradeMonth} in ['January',
'February','March'] then{rptAvgSpreadToCoverRatio.ExecuteToCoverDiff}

Place this in the detail section and then insert an average on this formula. If you use a conditional criterion with a summary field, it just says that if the criteria are met for the current record, show me the summary of all records, when what you really mean is show me the summary of all records that meet this criterion.

-LB

 

[junction123]

Hi. I tried this but I have some questions.

1) The formula cannot be placed in the detail as I have the actual values for ExecuteToCoverDiff and ResponseCount.

2) Currently I group by ResponseCount and I have a formula for New Page Before as not onfirstrecord.

This breaks each month on a new page for example

March 2009 and the values for ExecuteToCoverDiff display

Next page
January 2010
january 2009 and the values for ExecuteToCoverDiff display

I would like to have the values displayed on page 1 for for ExecuteToCoverDiff and ResponseCount

January 2009
February 2009
March 2009

1st Quarter 2009 Avg(ExecuteToCoverDiff) and Avg(ResponseCount)

April 2009
May 2009
June 2009

2nd Quarter 2009

Avg(ExecuteToCoverDiff) and Avg(ResponseCount)

The detail section already has the value for each month.

Thanks. Please let me know if more information is required for clarification.

 

[lbass]

Please show a few rows of detail level data with labels.

I'm assuming that each row has a month label and corresponding summaries as fields, so I don't see your point in #1 above. If you only want to summarize (average) certain months, you need to place my suggested formula in the detail section (suppress the formula) and insert an average at whatever group level you want the result, e.g., quarter.

I don't see why you would group on ResponseCount. I don't think you meant that.

-LB

 

[junction123]

Yes. I group by Trade Month and then by Trade Year.

Month ExecuteToCoverRatio ResponseCount
January 25.92 3.86
February 24.39 3.76
March 25.21 3.77
2009 Q1 25.16 3.80

April 19.27 3.88
May 18.26 3.94
June 13.87 4.10
2009 Q2 16.78 3.99

July 12.11 4.22
August 12.09 4.33
September 8.88 4.66
2009 Q3 10.87 4.43

October 7.51 5.16
November 6.94 5.07
December 6.88 5.04
(MTD as of 12/21/2009
2009 Q4 7.13 5.09

My output is currently not showing data but I did get as below on each page rather than every quarter.

Month ExecuteToCoverRatio ResponseCount
February 24.39 3.76

Next page

Month ExecuteToCoverRatio ResponseCount
January 25.92 3.86
2009

Month ExecuteToCoverRatio ResponseCount
January 21.92 2.86
2010

Not sure whether my grouping is right and I use a conditional formula for the TradeMonthName but the output is not the way i need it.

Thanks

 

[lbass]

Looks to me like you should group on Year, Quarter, and Month in that order. Then you should just be able to insert averages on the fields at the month quarter group level and these would appear in the Quarter group footer.

-LB

 

[lbass]

Meant to say:

insert averages on the fields at the quarter group level

-LB

 

[junction123]

I see.

There is no field for Quarter. There is a TradeMonth field with values 1, 2,3 for January, February and March and so on.

Then should I group by TradeYear and then by TradeMonth? Would this help group by every quarter or do i specify the Quarter by specifying the tradeMonth values say 1,2,3 for say January, February, March and then get the Quarter?

I am unable to figure out how to get the quarter groupings with the fields that I have .

Thanks

 

[junction123]

I am trying to add a QuarterByYear field to the backend stored procedure written in SQL 2005.

Then I can use this field in the report.

 

[lbass]

You can just create a formula to group on:

if {table.month} in 1 to 3 then "Qtr 1" else
if {table.month} in 4 to 6 then "Qtr 2" else
if {table.month} in 7 to 9 then "Qtr 3" else
if {table.month} in 10 to 12 then "Qtr 4"

-LB

 

[junction123]

I added a QuarterByYear field. Now I group By Trade Year and I get the following on the first page for 2009.

TradeMonthname ExecuteToCoverRatio ResponseCount
February 24.66 3.05
January 24.42 3.28
March 21.45 3.07


The next page I get for 2010

TradeMonthName ExecuteToCoverRatio ResponseCount
January 17.36 3.99

Why cant I get all the Months in one page, maybe I have a page break?

Also I need each quarter on the first page for 2009.

Thanks

 

[junction123]

LB,

I did not see your post on the formula.
Since TradeMonth is a string I used the below in the formula. i will try the formula first and then test the backend field as well.

Thanks

if {rptAvgSpreadToCoverRatio.TradeMonth} in ['1,2,3'] then "Qtr 1" else
if {rptAvgSpreadToCoverRatio.TradeMonth} in ['4,5,6'] then "Qtr 2" else
if {rptAvgSpreadToCoverRatio.TradeMonth} in ['7,8,9'] then "Qtr 3" else
if {rptAvgSpreadToCoverRatio.TradeMonth} in ['10,11,12'] then "Qtr 4

 

[lbass]

You have to enclose each value in quotes, not the series. or use:

if val({rptAvgSpreadToCoverRatio.TradeMonth}) in 1 to 3 then, etc.

If you just group on year then on the quarter formula, you can set new page after on the year group footer.

-LB

 

[junction123]

I used the Database field and I gett he following output

TradeMonthName ExecuteToCoverRatio ResponseCount

February 24.66 3.05
January 24.42 3.28
March 21.45 3.07


2009 Q1 AVG(24.66,24.42,21.45) AVG(3.05,3.28,3.07)

Now I need the Aggregated values for both ExecuteToCoverRatio ResponseCount .

I will use the formula you suggested, put it in the detail section and average on that value.

If {rptAvgSpreadToCoverRatio.TradeMonth} in ['January',
'February','March'] then{rptAvgSpreadToCoverRatio.ExecuteToCoverDiff}


Hopefully I get the Aggregate value. But I still do not know why i am not gettign the remaining months grouped.

Thanks for your help.

 

[junction123]

Using New page After on the group for QuarterByYear, leaves blank pages after page 1 and after page 3. I tried a formula under section expert "onlastrecord" and this eliminated one blank page.

How to get rid of the Blank page? Should I try Suppress Blank section or are the blank pages due to the New Page After being checked?

thanks

 

[lbass]

You need to go into the section expert and check to see if you have any new page after or new page before set on other sections. I think you should add a group #1 on year, and just use new page after on that footer. In the formula area for that add:

not onlastrecord

There should be no blank pages if you do this correctly.

-LB

 

[junction123]

Thanks. I fixed the Blank page. Now I have two pages which is perfect!

2009 data on first page and 2010 data on second page.

But I still cannot figure out why I do not see data for April may June and so on.

 

[lbass]

You mean you are ONLY seeing the first three months? What is your record selection formula?

-LB

 

[junction123]

Yes. My output is for Page 1

TradeMonthName ExpectedCoverToRatio ResponseCount
February 24.66 3.05
January 24.42 3.28
March 21.45 3.07


2009 Q1 24.04

Page 2

TradeMonthName ExpectedCoverToRatio ResponseCount
January 17.36 3.99

2010 Q1 24.04


Formula for Avg
IF {rptAvgSpreadToCoverRatio.TradeYear} = {rptAvgSpreadToCoverRatio.TradeYear}
THEN Average({rptAvgSpreadToCoverRatio.ExecuteToCoverDiff})


Oh and I have nothing for the record selection formula.

 

[lbass]

Please reread my first post. Your conditional formula is incorrect.

You had better also explain how you are linking your tables.

Please also verify the fields you are grouping on.

-LB