3 Replies Latest reply: Apr 14, 2017 2:04 PM by Michael Andrews RSS

    Trying to reproduce average of var in sql

    Michael Andrews

      Hi, I'm trying to reproduce an SSRS report we have in qlik, and I'm running into some issue with some math I just can't seem to pin down. We have a variable we create in sql like so

       

      SELECT @itemVarianceAvg = AVG(is_item_variance)
             FROM
             (
                  SELECT VAR(i_item_score) AS is_item_variance
                       , i_item_name
                  FROM #items
                  WHERE i_exam_form = @currentForm
                  GROUP BY i_item_name
             ) AS S
      
      

       

      The qlik functions I'm trying to piece together look like this

       

      Avg(Aggr(Sqr(Stdev(ItemScore)), ItemName))

       

      The issue I'm running into is that the numbers I get in qlik do not match what I get in sql server, when running the reports on similar data sets. In sql server it's 0.262459841222526 but in qlik I get 0.21

       

      Can anyone spot something I might have wrong in my qlik functions to get a different number?

       

      **Edit**

       

      So I did discover something, if I filter my report down to a single form, my numbers are correct. So, I think I need to add into that function, some way to filter per form. See my attachment for further details.

       

      Message was edited by: Michael Andrews