Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
If you need to calculate the variance per form, try adding the field to the aggr() dimensions.
Avg(Aggr(Sqr(Stdev(ItemScore)), ExamFormField, ItemName))
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
If you need to calculate the variance per form, try adding the field to the aggr() dimensions.
Avg(Aggr(Sqr(Stdev(ItemScore)), ExamFormField, ItemName))
Thanks!