Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have an object that tracks annual transitions from one rating category to another, based on the rating start and end date.
Below is the formula used to produce the transition.
sum(
if(#FirstDate<#Pools
and #LastDate>=#Pools
and [Rating Symbol Sort]<=21
and #EOPFirstDate<AddYears(#Pools,1)
and #EOPLastDate>=AddYears(#Pools,1)
and #Pools<=addyears('1/1/2014',-1)
,1,0))
/sum(TOTAL<[RatingCategory],Pools>
if(#FirstDate<#Pools
and #LastDate>=#Pools
and [Rating Symbol Sort]<=21
and #EOPFirstDate<AddYears(#Pools,1)
and #EOPLastDate>=AddYears(#Pools,1)
and #Pools<=addyears('1/1/2014',-1)
,1,0))
There are three tables involved:
BOP:
#FirstDate Start Date of the Rating
#LastDate End Date of the Rating
Rating Symbol Sort Numerical Rank of the Rating
RatingCategory Display Name of the Rating
EOP:
#EOPFirstDate Start Date of the Rating
#EOPLastDate End Date of the Rating
Pools:
#Pools List of N January Firsts in Numerical Form
Pools List of N January Firsts in Display Form
I need to calculate the standard deviation of the transitions, and can't figure out how to do it.
Please, any help would be greatly appreciated.
Thank you,
Econatog
Your tables seem to have no fields in common. That would mean your sum expression would be working on cartesian products. The results would be meaningless. And so would any stdev.
Forgot the common field, and have added it below.
BOP:
#FirstDate Start Date of the Rating
#LastDate End Date of the Rating
Rating Symbol Sort Numerical Rank of the Rating
RatingCategory Display Name of the Rating
EntityID
EOP:
#EOPFirstDate Start Date of the Rating
#EOPLastDate End Date of the Rating
EntityID
Pools:
#Pools List of N January Firsts in Numerical Form
Pools List of N January Firsts in Display Form
Does the table Pools contain only one record? Or are you still working with cartesian products?
Pools contains a list of dates. For this example, say each January first from 2000 to 2013.
Any new ideas? Trying to beat a deadline
No, because I have no idea what you're trying to do. As far as I can tell you want to do something with sets of data that result in a cartesian product. I don't think that will ever result in something useful, but good luck anyway.
This is the result of of of these formula:
Q | W | E | R | T | Y | U | I | O | |
Q | 0.87 | 0.08 | 0.01 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.04 |
W | 0.00 | 0.84 | 0.10 | 0.01 | 0.00 | 0.00 | 0.00 | 0.00 | 0.05 |
E | 0.00 | 0.02 | 0.88 | 0.05 | 0.00 | 0.00 | 0.00 | 0.00 | 0.05 |
R | 0.00 | 0.00 | 0.03 | 0.86 | 0.04 | 0.01 | 0.00 | 0.00 | 0.07 |
T | 0.00 | 0.00 | 0.00 | 0.05 | 0.76 | 0.07 | 0.00 | 0.01 | 0.10 |
Y | 0.00 | 0.00 | 0.00 | 0.00 | 0.06 | 0.73 | 0.05 | 0.03 | 0.12 |
U | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.16 | 0.40 | 0.28 | 0.16 |
The Q to Q transition represents the ending Qs divided by the starting Qs from the N years in Pools.
I need to calculate the standard deviation of each of those ratios.