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

[OmarBenSalem]

Hi Michael,

You can always use what you're using in SQL then load the whole thing in Qlik.

For you example, no need to change a thing, just do as follow:

LOAD *;

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

with that, you'll do the work in the Select part, as you were doing then load what you wanna load in Qlik.

Hope this helps

[swuehl · Accepted solution]

If you need to calculate the variance per form, try adding the field to the aggr() dimensions.

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

[michael_andrews]

Thanks!