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

 

[junction123]

I have the following report output on crystal: Where All is All Dealer, Old is Old Dealer, New is New Dealer. The vales are specified in the join below. Let me know if you need the complete back end code pasted? I did not paste the entire column name due to lack of space but hope this format helps.

All Old New

Month Exec Resp Exec Resp Exec Resp
To Cover Count To Count To Count
Diff Diff Diff
January 25.92 3.86 27.37 3.28 25.89 3.88
February 24.39 3.76 22.23 3.09 24.46 3.80
March 25.21 3.77 26.85 3.27 25.12 3.79

2009 Q1 25.16 3.80 25.18 3.20 25.14 3.82

I added the DealerTypeDate column which holds values 'Old' and 'New' to teh #OldDealer and #Newdealer tables and grouped by the DealerTypeDate field as well. Then I added the condition where All.DealerTypeDate = New.DealerTypeDate and where All.DealerTypeDate = Old.DealerTypeDate to both LEFT OUTER JOINS in the final select. Now I see NULL for values that do not match.


For the below final output query for the report I get the output as shown below.

Columns are :

TradeMonthName
TradeYear
ExecteToCoverDiff_ALL
ResponseCount_ALL
ExecteToCoverDiff_OLD
ResponseCount_OLD
ExecteToCoverDiff_NEW
ResponseCount_NEW
TradeMonth
QuarterByYear
DealerTypeDate


But Report should show below columns as stated in my prior post

TradeMonthName
ExecteToCoverDiff_ALL
ResponseCount_ALL
ExecteToCoverDiff_OLD
ResponseCount_OLD
ExecteToCoverDiff_NEW
ResponseCount_NEW

I am grouping by :

TradeYear
QuarterByYear,
TradeMonthName

I am using the formula for the Average for example January, February, March Quarter 1.


IF {rptAvgSpreadToCoverRatio.TradeMonth} IN ('1' to'3')
AND {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff} = {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff} THEN Average({rptAvgSpreadToCoverRatio.ExecuteToCoverDiff})


The report Aggregate vale appears correct now. For example for values

ExecuteToCoverRatio

24.66
24.42
21.45

I get Q 1 Average as 23.51. I can probably fix the Average value for ResponseCount as well.

But why I am not seeing the remaing months for the year 2009? How to bring these months? Thanks a lot for your help! Appreciate it.

The backend code returns


select
alld.TradeMonthName,
alld.TradeYear,
alld.ExecuteToCoverDiff ,
alld.ResponseCount ,
New.ExecuteToCoverDiff ,
New.ResponseCount ,
Old.ExecuteToCoverDiff ,
Old.ResponseCount ,
alld.TradeMonth ,
alld.QuarterByYear,
alld.DealerTypeDate
from
#AllDealer alld
LEFT OUTER JOIN
#NewDealer New
ON
alld.TradeMonthName = New.TradeMonthName
AND alld.TradeYear = New.TradeYear
AND alld.DealerTypeDate = New.DealerTypeDate
LEFT OUTER JOIN
#OldDealer Old
ON
alld.TradeMonthName = Old.TradeMonthName
AND alld.TradeYear = Old.TradeYear
AND alld.DealerTypeDate = Old.DealerTypeDate

Trade Trade Execute Response Execute Response Execute Response Trade Quarter Dealer
Month Year To Count_ALL To Count_NEW To Count_OLD Month By Type
Name CoverDiff_ALL CoverDiff_NEW CoverDiff_OLD YearDate

January 2009 24.4241149147541 3.28196721311475 NULL NULL 24.4241149147541 3.28196721311475 01 Q1 OLD
February 2009 24.6610613651376 3.04770642201835 NULL NULL 24.6610613651376 3.04770642201835 02 Q1 OLD
March 2009 21.4489570791075 3.06896551724138 NULL NULL 21.4489570791075 3.06896551724138 03 Q1 OLD
April 2009 22.797960170068 3.2687074829932 NULL NULL 22.797960170068 3.2687074829932 04 Q2 OLD
May 2009 25.1883597044335 3.33497536945813 NULL NULL 25.1883597044335 3.33497536945813 05 Q2 OLD
June 2009 19.4323144223827 3.76173285198556 NULL NULL 19.4323144223827 3.76173285198556 06 Q2 OLD
July 2009 71.918232464455 4.05687203791469 NULL NULL 71.918232464455 4.05687203791469 07 Q3 OLD
August 2009 18.2744382670808 3.82298136645963 NULL NULL 18.2744382670808 3.82298136645963 08 Q3 OLD
September 2009 14.3352155945946 4.17027027027027 NULL NULL 14.3352155945946 4.17027027027027 09 Q3 OLD
October 2009 0.200000000000003 9.5 0.200000000000003 9.5 NULL NULL 10 Q4 NEW
October 2009 23.3343639556962 4.51898734177215 NULL NULL 23.3343639556962 4.51898734177215 10 Q4 OLD
November 2009 6.06 3.6 6.06 3.6 NULL NULL 11 Q4 NEW
November 2009 17.0942585403424 4.18807339449541 NULL NULL 17.0942585403424 4.18807339449541 11 Q4 OLD
December 2009 13.3842857142857 4.90476190476191 13.3842857142857 4.90476190476191 NULL NULL 12 Q4 NEW
December 2009 12.4545201873831 4.20089285714286 NULL NULL 12.4545201873831 4.20089285714286 12 Q4 OLD
January 2010 11.4792311538462 3.92307692307692 11.4792311538462 3.92307692307692 NULL NULL 01 Q1 NEW
January 2010 17.6750053985208 3.98975409836066 NULL NULL 17.6750053985208 3.98975409836066 01 Q1 OLD

 

[junction123]

I got the Data for Q1 as below:

Month Execute Response
ToCover Count
DiffRatio

February 24.66 3.05
January 24.42 3.28
March 21.45 3.07
2009 Q1 23.51 3.13

Where 23.51 and 3.13 are the aggregated values for the ExecuteToCoverRatio and Response Count.

I have created individual formulae for the remaining columns for New and Old for the Q1. I am not sure whether I can combine these into one formula but here are my formulae for each scenario.

Avg(ExecuteToCoverDiffRatio) For ALL and Q1
IF {rptAvgSpreadToCoverRatio.TradeMonth} IN ('1' to'3')
AND {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff} = {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff}
THEN Average({rptAvgSpreadToCoverRatio.ExecuteToCoverDiff})

Avg(ExecuteToCoverDiffRatio)_NEW
For NEW and Q1
IF {rptAvgSpreadToCoverRatio.TradeMonth} IN ('1' to'3')
AND {rptAvgSpreadToCoverRatio.DealerTypeDate} = 'NEW'
AND {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff(1)} = {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff(1)}
THEN Average({rptAvgSpreadToCoverRatio.ExecuteToCoverDiff(1)})

Avg(ExecuteToCoverDiffRatio)_OLD fro Old and Q1
IF {rptAvgSpreadToCoverRatio.TradeMonth} IN ('1' to'3')
AND {rptAvgSpreadToCoverRatio.DealerTypeDate} = 'OLD'
AND {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff(2)} = {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff(2)}
THEN Average({rptAvgSpreadToCoverRatio.ExecuteToCoverDiff(2)})

I have similar three formulae for ResponseCount for Q1.

If there is a way to combine all in one then this would reduce the number of formuale and look less convoluted.

I am stuck on how to get the remaining months Q2, Q3 and Q4 which are returned by the database.

Thanks for your help.

 

[junction123]

In addition to the above I have one more question:

If for a year I have two rows of database data for Month like so:

January 2010 11.47923115 3.923076923 11.47923115 3.923076923 NULL NULL 1 Q1 NEW
January 2010 17.6750054 3.989754098 NULL NULL 17.6750054 3.989754098 1 Q1 OLD

Report Output Shows the following based on my formula:

Month Exec Resp Exec Resp Exec Resp
To Count To Count To Count
Cover Cover Cover
Diff Diff_ Diff_OLD
January 17.68 3.99 17.68 3.99
2010 Q117.68 3.99 11.48 3.92 17.68 3.99

The value 11.48 and 3.92 are the values for NEW. Why are these values not showing up in the month row detail section? They do show up for aggregate values which is fine.

What grouping should I change or formula should I change?

My grouping is by TradeYear , then by QuarterByYear then by TradeMonthName.

Thanks for your help again.














select
TradeMonthName,
TradeYear,
ExecuteToCoverDiff = avg(LevelDiff),
ResponseCount = avg(convert(float, ResponseCount)),
TradeMonth,
QuarterByYear,
DealerTypeDate
INTO #OldDealer
from
#Temp
WHERE DealerTypeDate = 'OLD'
group by
/*TradeMonthName,
TradeYear,
TradeMonth,
QuarterByYear*/
TradeYear,
QuarterByYear,
TradeMonthName,
TradeMonth,
DealerTypeDate


order by
TradeMonth,
TradeYear

select
TradeMonthName,
TradeYear,
ExecuteToCoverDiff = avg(LevelDiff),
ResponseCount = avg(convert(float, ResponseCount)),
TradeMonth,
DealerTypeDate --added to fix incorrect data
INTO #NewDealer
from
#Temp
WHERE DealerTypeDate = 'NEW'
group by
/*TradeMonthName,
TradeYear,
TradeMonth,
QuarterByYear*/
TradeYear,
QuarterByYear,
TradeMonthName,
TradeMonth,
DealerTypeDate
order by
TradeMonth,
TradeYear

select
TradeMonthName,
TradeYear,
ExecuteToCoverDiff = avg(LevelDiff),
ResponseCount = avg(convert(float, ResponseCount)),
TradeMonth,
QuarterByYear,
DealerTypeDate -- added DealerTypeDate because All Dealer should contain both New and Old
INTO #AllDealer
from
#Temp
group by
/*TradeMonthName,
TradeYear,
TradeMonth,
QuarterByYear */
TradeYear,
QuarterByYear,
TradeMonthName,
TradeMonth,
DealerTypeDate ---- added DealerTypeDate because All Dealer should contain both New and Old
order by
TradeYear,
TradeMonth

select
alld.TradeMonthName,
alld.TradeYear,
alld.ExecuteToCoverDiff ,
alld.ResponseCount ,
New.ExecuteToCoverDiff ,
New.ResponseCount ,
Old.ExecuteToCoverDiff ,
Old.ResponseCount ,
alld.TradeMonth ,
alld.QuarterByYear,
alld.DealerTypeDate
from
#AllDealer alld
LEFT OUTER JOIN
#NewDealer New
ON
alld.TradeMonthName = New.TradeMonthName
AND alld.TradeYear = New.TradeYear
AND alld.DealerTypeDate = New.DealerTypeDate
LEFT OUTER JOIN
#OldDealer Old
ON
alld.TradeMonthName = Old.TradeMonthName
AND alld.TradeYear = Old.TradeYear
AND alld.DealerTypeDate = Old.DealerTypeDate

 

[junction123]

I tried using the below in the Record Selection to bring in the values missing in the detail section but it did not work.

IF {rptAvgSpreadToCoverRatio.TradeMonth} IN ('1' To '3')
THEN {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff} = {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff}
AND {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff(1)} = {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff(1)}
AND {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff(2)} = {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff(2)}
AND {rptAvgSpreadToCoverRatio.ResponseCount} = {rptAvgSpreadToCoverRatio.ResponseCount}
AND {rptAvgSpreadToCoverRatio.ResponseCount(1)} = {rptAvgSpreadToCoverRatio.ResponseCount(1)}
AND {rptAvgSpreadToCoverRatio.ResponseCount(2)} = {rptAvgSpreadToCoverRatio.ResponseCount(2)}

 

[junction123]

Hi,

My Selection formula for Group was messing things up and not showing the remaining months data. I commented these and now I see all data grouped by Quarter.

But Two things:

1) The Month order is not sorted. How can I achieve this?2) When NEW values for ExecToCoverDiff match with the ALL values, just the new values are returned. The OLD values are NULL which is fine. But the ALL values that match with the NEW values are not displayed. Can you help tell me why?

example: Look at October 2009, November 2009, December 2009 and January 2010. The values for NEW are displayed but the corresponding matching values for ALL are not displayed. I do not understand why. Thanks!

Month Exec Resp Exec Resp Exec Resp
To Count To Count To Count
Cover Cover Cover
Diff Diff_NEW Diff_OLD


February 24.66 3.05 24.66 3.05
January 24.42 3.28 24.42 3.28
March 21.45 3.07 21.45 3.07
2009 Q1 23.51 3.13 23.51 3.13
April 22.80 3.27 22.80 3.27
June 19.43 3.76 19.43 3.76
May 25.19 3.33 25.19 3.33
2009 Q2 22.47 3.46 22.47 3.46
August 18.27 3.82 18.27 3.82
July 71.92 4.06 71.92 4.06
September14.34 4.17 14.34 4.17
2009 Q3 34.84 4.02 34.84 4.02
December 13.38 4.90
December 12.45 4.20 12.45 4.20
November 6.06 3.60
November 17.09 4.19 17.09 4.19
October 0.20 9.50
October 23.33 4.52 23.33 4.52
2009 Q4 17.63 4.30 6.55 6.00 17.63 4.30
January 11.48 3.92
January 17.68 3.99 17.68 3.99
2010 Q1 17.68 3.99 11.48 3.92 17.68 3.99


//IF {rptAvgSpreadToCoverRatio.TradeMonthName} IN ['January','February','March']
//THEN {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff} = {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff}
//ELSE
//IF {rptAvgSpreadToCoverRatio.TradeMonthName} IN ['April','May','June']
//THEN {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff(1)} = {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff(1)}

 

[junction123]

Just so you know, Below is the original sheet that users use driven by adhoc queries. I notice that each month is Distinct and not repeated as in my case for Octover November, Decemer 2009 and January 2010.

In my report, and database, I return the 4 rows for NEW but I need to suppress the print of the MonthName if it is the same but still show matching values for NEW and ALL for that TradeYear. Thanks for your help.

January 25.92 3.86 27.37 3.28 25.89 3.88
February 24.39 3.76 22.23 3.09 24.46 3.80
March 25.21 3.77 26.85 3.27 25.12 3.79

2009 Q1 25.16 3.80 25.18 3.20 25.14 3.82

April 19.27 3.88 19.35 3.54 19.28 3.89
May 18.26 3.94 17.22 3.49 18.29 3.95
June 13.87 4.10 14.36 3.63 13.86 4.12

2009 Q2 16.78 3.99 16.79 3.56 16.78 4.00

July 12.11 4.22 34.62 3.58 11.57 4.24
August 12.09 4.33 17.29 3.85 11.93 4.35
September8.88 4.66 14.01 4.23 8.73 4.68

2009 Q3 10.87 4.43 20.62 3.93 10.59 4.44

October 7.51 5.16 14.28 4.65 7.21 5.18
November 6.94 5.07 11.68 4.64 6.57 5.10
December 6.88 5.04 9.14 4.60 6.79 5.06
(MTD as of 12/21/2009
2009 Q4 7.13 5.09 11.60 4.63 6.86 5.12

 

[junction123]

I have six formulae three each for ExecToCoverDiff (ALL, NEW, OLD) and three each for ResponseCount(ALL,NEW,OLD).
example below.

Originally I had just one formula for each for TradeMonth 1 To 3. There seems to be no difference in the data though.

I need to still fix the following:
1)Order the trade Month Name
2) Do not show repeated months for example we may have All ExecToCoverDiff and ResponseCount values matching for NEW and OLD for the same month
3) Show the All values for ExecToCoverDiff and ResponseCount when it matches NEW values.

IF {rptAvgSpreadToCoverRatio.TradeMonth} IN ('1' to'3')
AND {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff} = {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff}
THEN Average({rptAvgSpreadToCoverRatio.ExecuteToCoverDiff})
ELSE
IF {rptAvgSpreadToCoverRatio.TradeMonth} IN ('4' to'6')
AND {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff} = {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff}
THEN Average({rptAvgSpreadToCoverRatio.ExecuteToCoverDiff})

ELSE
IF {rptAvgSpreadToCoverRatio.TradeMonth} IN ('7' to'9')
AND {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff} = {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff}
THEN Average({rptAvgSpreadToCoverRatio.ExecuteToCoverDiff})

ELSE
IF {rptAvgSpreadToCoverRatio.TradeMonth} IN ('10' to'12')
AND {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff} = {rptAvgSpreadToCoverRatio.ExecuteToCoverDiff}
THEN Average({rptAvgSpreadToCoverRatio.ExecuteToCoverDiff})

 

[junction123]

I was able to fix 1) below. I am not sure how to workaround 2) and 3)

I need to still fix the following:
1)Order the trade Month Name
2) Do not show repeated months for example we may have All ExecToCoverDiff and ResponseCount values matching for NEW and OLD for the same month
3) Show the All values for ExecToCoverDiff and ResponseCount when it matches NEW values.

 

[lbass]

I am sorry, but I am unwilling to wade through all this, but let me point out that you are still ignoring my very first post and how to work with conditional formulas.

-LB