0

I have to calculate StDev for my column groups. The problem is that it take in calculations null values. I use this: 

=IIF(Fields!estNumerique.Value = 0, "n/a",Replace( Format( StDev( cDec( Replace( IIF(Fields!estNumerique.Value = 0, nothing, Fields!result.Value) ,",",".")))   ,"0.000") ,".",","))

And I get this result: result

I calculated Average wiht following method, but for StDev I cannnot find the solution:

=IIf(Fields!SomeField.Value = 0, 0, Fields!SomeOtherField.Value / IIf(Fields!SomeField.Value = 0, 1, Fields!SomeField.Value))

Thank you for your help!

Maria Tacu
  • 3
  • 1

1 Answers1

1

The StDev function ignores NULL values. You are doing good by converting the 0's to NULL. The problem is that your CDec function is changing the NULL's back to 0. So just remove that.

EDIT:

Try this:

=IIF(Fields!estNumerique.Value = 0, "n/a",Replace( Format( StDev( Replace( IIF(Fields!estNumerique.Value = 0, Nothing, CDec(Fields!result.Value)) ,",","."))   ,"0.000") ,".",","))
StevenWhite
  • 5,907
  • 3
  • 21
  • 46
  • Hello, StevenWhite, if I remove CDec, it return error : a numeric aggregate function on data that is not numeric. I get a string value in result and I have to decide if it is numeric or not. So, I have to have this Cdec in my expression. Can you propose an other solution? – Maria Tacu Mar 01 '17 at 19:31
  • Yeah, just move the `CDec` to be around the field name inside the `IIf` statement. That way it does the type conversion before ignoring the 0's. – StevenWhite Mar 01 '17 at 20:20
  • Unfortunately, it doesn't work in my case. As result, I have added a column in my query that returns only numerical value and I have changed my expression for: =IIF(Fields!estNumerique.Value = 0, "n/a", Format(StDev(Fields!result_num.Value), "0.000")) – Maria Tacu Mar 02 '17 at 15:53
  • OK, glad we figured out the issue. Also, you may need to wrap a `Max` function around that first "Fields!estNumerique.Value". Otherwise, you will get "n/a" if the **First** value happens to be 0 but the others aren't. – StevenWhite Mar 02 '17 at 15:58