Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
michael_andrews
New Contributor III

Trying to reproduce average of var in sql

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Trying to reproduce average of var in sql

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

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

3 Replies
OmarBenSalem
Esteemed Contributor

Re: Trying to reproduce average of var in sql

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

MVP
MVP

Re: Trying to reproduce average of var in sql

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
New Contributor III

Re: Trying to reproduce average of var in sql

Thanks!