Standard Deviation of Transitions

[#1]

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

[Gysbert_Wassenaar]

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.

[#3]

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

[Gysbert_Wassenaar]

Does the table Pools contain only one record? Or are you still working with cartesian products?

[#5]

Pools contains a list of dates. For this example, say each January first from 2000 to 2013.

[#6]

Any new ideas? Trying to beat a deadline

[Gysbert_Wassenaar]

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.

[#8]

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.