Find the variance in a group

  • 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?

  • 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
  • There aren't any nulls. I've attached a screen shot of what the report looks like. Just look at the Members field.

  • 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
  • Yes. I need an expression for SSRS.

  • 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
  • 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))

  • 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
  • I used the term loosely. Basically, I want to subtract one row from another in the same field. Very similar to Sum().

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

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