Find the variance in a group

Question

Find the variance in a group

NineIron

SSChampion

Points: 12529
More actions
July 18, 2018 at 6:18 am

#372315

I have a very simple report grouped by "Payer". There are two rows of data. One for 2017 and the other for 2018. I'm trying to find the variance of certain metrics between these two dates. One of the metrics is membership. I've tried to use the function Var but, I don't get the expected result. For testing purposes I used Sum and got the expected result. Any thoughts?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

NineIron - Wednesday, July 18, 2018 6:18 AM
I have a very simple report grouped by "Payer". There are two rows of data. One for 2017 and the other for 2018. I'm trying to find the variance of certain metrics between these two dates. One of the metrics is membership. I've tried to use the function Var but, I don't get the expected result. For testing purposes I used Sum and got the expected result. Any thoughts?

Do you have nulls? Those might affect the result.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

NineIron SSChampion Points: 12529 More actions · Answer 2

There aren't any nulls. I've attached a screen shot of what the report looks like. Just look at the Members field.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

You might be using the wrong function as well that might not correspond to the formula that you're trying to get.
CREATE TABLE #Heights( Height DECIMAL(16,10))

INSERT INTO #Heights ( Height ) VALUES(600),(470),(170),(430),(300);

SELECT SUM(SQUARE(x.Height - y.Mean_Height))/COUNT(*) - 1, VAR(x.Height), --Variance of a sample SUM(SQUARE(x.Height - y.Mean_Height))/COUNT(*), VARP(x.Height) -- Variance of a population FROM #Heights x CROSS JOIN( SELECT AVG(Height) FROM #Heights)y(Mean_Height)

GO DROP TABLE #Heights

EDIT: And I just now noticed that you're working on SSRS and not T-SQL. The solution might be similar, hopefully.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

NineIron SSChampion Points: 12529 More actions · Answer 4

NineIron

SSChampion

Points: 12529

July 18, 2018 at 7:15 am

#1997988

Yes. I need an expression for SSRS.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

NineIron - Wednesday, July 18, 2018 7:15 AM
Yes. I need an expression for SSRS.

Have you tried getting the correct value with VarP() function?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

NineIron SSChampion Points: 12529 More actions · Answer 6

Yes. No luck.
I found this using Google but, the Value for the DateRange will change each month. I get the expected results, though.

=Sum(IIF(Fields!DateRange.Value="Jan - May 2018",Fields!Members.Value,0))
-
Sum(IIF(Fields!DateRange.Value="Jan - May 2017",Fields!Members.Value,0))

Luis Cazares SSC Guru Points: 183698 More actions · Answer 7

NineIron - Wednesday, July 18, 2018 7:39 AM
Yes. No luck.
I found this using Google but, the Value for the DateRange will change each month. I get the expected results, though.
=Sum(IIF(Fields!DateRange.Value="Jan - May 2018",Fields!Members.Value,0))
-
Sum(IIF(Fields!DateRange.Value="Jan - May 2017",Fields!Members.Value,0))

That's not a variance calculation. That's a difference between 2 values.
Variance is a statistical calculation and explained in here (and many other places): https://www.mathsisfun.com/data/standard-deviation.html

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

NineIron SSChampion Points: 12529 More actions · Answer 8

I used the term loosely. Basically, I want to subtract one row from another in the same field. Very similar to Sum().