Complex CASE statement

I'm seeing odd behavior from a CASE statement.  The example below is the easiest way to explain what's going on.

In the example below [x1], [x2], [n1], [n2], and [p.value] evalutate to expected values...

x1 = 43

x2 = 166

n1 = 7311

n2 = 36905

p.value = 0.115098

However [cased p.value] results in 0.137874.  (else 999 is just an easy visual indicator as I develop and has no meaning)

Sum(If([Category]="Alpha",[Count])) as [x1], 
Sum(If([Category]="Beta",[Count])) as [x2], 
Sum(If(([Category]="Alpha") AND ([Subcategory]<>"Gamma") AND ([Subcategory]<>"Delta"),[Count])) OVER (Parent(Parent([Axis.Rows]))) as [n1], 
Sum(If(([Category]="Beta") AND ([Subcategory]<>"Gamma") AND ([Subcategory]<>"Delta"),[Count])) OVER (Parent(Parent([Axis.Rows]))) as [n2], 
2 * NormDist(Abs(((Sum(If([Category]="Alpha",[Count])) / Sum(If(([Category]="Alpha") AND ([Subcategory]<>"Gamma") AND ([Subcategory]<>"Delta"),[Count])) OVER (Parent(Parent([Axis.Rows])))) - (Sum(If([Category]="Beta",[Count])) / Sum(If(([Category]="Beta") AND ([Subcategory]<>"Gamma") AND ([Subcategory]<>"Delta"),[Count])) OVER (Parent(Parent([Axis.Rows]))))) / Sqrt((Sum(If([Category]="Alpha",[Count])) + Sum(If([Category]="Beta",[Count]))) / (Sum(If(([Category]="Alpha") AND ([Subcategory]<>"Gamma") AND ([Subcategory]<>"Delta"),[Count])) OVER (Parent(Parent([Axis.Rows]))) + Sum(If(([Category]="Beta") AND ([Subcategory]<>"Gamma") AND ([Subcategory]<>"Delta"),[Count])) OVER (Parent(Parent([Axis.Rows])))) * (1 - ((Sum(If([Category]="Alpha",[Count])) + Sum(If([Category]="Beta",[Count]))) / (Sum(If(([Category]="Alpha") AND ([Subcategory]<>"Gamma") AND ([Subcategory]<>"Delta"),[Count])) OVER (Parent(Parent([Axis.Rows]))) + Sum(If(([Category]="Beta") AND ([Subcategory]<>"Gamma") AND ([Subcategory]<>"Delta"),[Count])) OVER (Parent(Parent([Axis.Rows])))))) * ((1.0 / Sum(If(([Category]="Alpha") AND ([Subcategory]<>"Gamma") AND ([Subcategory]<>"Delta"),[Count])) OVER (Parent(Parent([Axis.Rows])))) + (1.0 / Sum(If(([Category]="Beta") AND ([Subcategory]<>"Gamma") AND ([Subcategory]<>"Delta"),[Count])) OVER (Parent(Parent([Axis.Rows])))))))) as [p.value],
case
  when SN(Sum(If([Category]="Alpha",[Count])), 0) > 5 then
    2 * NormDist(Abs(((Sum(If([Category]="Alpha",[Count])) / Sum(If(([Category]="Alpha") AND ([Subcategory]<>"Gamma") AND ([Subcategory]<>"Delta"),[Count])) OVER (Parent(Parent([Axis.Rows])))) - (Sum(If([Category]="Beta",[Count])) / Sum(If(([Category]="Beta") AND ([Subcategory]<>"Gamma") AND ([Subcategory]<>"Delta"),[Count])) OVER (Parent(Parent([Axis.Rows]))))) / Sqrt((Sum(If([Category]="Alpha",[Count])) + Sum(If([Category]="Beta",[Count]))) / (Sum(If(([Category]="Alpha") AND ([Subcategory]<>"Gamma") AND ([Subcategory]<>"Delta"),[Count])) OVER (Parent(Parent([Axis.Rows]))) + Sum(If(([Category]="Beta") AND ([Subcategory]<>"Gamma") AND ([Subcategory]<>"Delta"),[Count])) OVER (Parent(Parent([Axis.Rows])))) * (1 - ((Sum(If([Category]="Alpha",[Count])) + Sum(If([Category]="Beta",[Count]))) / (Sum(If(([Category]="Alpha") AND ([Subcategory]<>"Gamma") AND ([Subcategory]<>"Delta"),[Count])) OVER (Parent(Parent([Axis.Rows]))) + Sum(If(([Category]="Beta") AND ([Subcategory]<>"Gamma") AND ([Subcategory]<>"Delta"),[Count])) OVER (Parent(Parent([Axis.Rows])))))) * ((1.0 / Sum(If(([Category]="Alpha") AND ([Subcategory]<>"Gamma") AND ([Subcategory]<>"Delta"),[Count])) OVER (Parent(Parent([Axis.Rows])))) + (1.0 / Sum(If(([Category]="Beta") AND ([Subcategory]<>"Gamma") AND ([Subcategory]<>"Delta"),[Count])) OVER (Parent(Parent([Axis.Rows]))))))))
  else
    999
end as [cased p.value]

Any ideas why [cased p.value] is yielding different results than [p.value]?

1 Comment

You would need to provide a sample data set or .dxp file with the data embedded so that we can understand what is going on here.

 

And how are you determining the "correct" p.value should be 0.115098 ?

Sean Riley - Apr 27, 2016 - 6:56am
+ Add a Comment
Login