topic Re: Standard Deviation of Transitions in QlikView

Standard Deviation of Transitions

Tue, 11 Nov 2014 20:32:04 GMT

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

Re: Standard Deviation of Transitions

Gysbert_Wassenaar — Wed, 12 Nov 2014 10:29:33 GMT

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.

Re: Standard Deviation of Transitions

Wed, 12 Nov 2014 14:48:12 GMT

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

Re: Standard Deviation of Transitions

Gysbert_Wassenaar — Wed, 12 Nov 2014 14:50:35 GMT

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

Re: Standard Deviation of Transitions

Wed, 12 Nov 2014 14:52:21 GMT

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

Re: Standard Deviation of Transitions

Wed, 12 Nov 2014 15:39:11 GMT

Any new ideas? Trying to beat a deadline

Re: Standard Deviation of Transitions

Gysbert_Wassenaar — Wed, 12 Nov 2014 17:31:40 GMT

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.

Re: Standard Deviation of Transitions

Wed, 12 Nov 2014 17:43:30 GMT

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.