Calculating a variance is like applying an aggregation function to a set of field values / records.
If you want to calculate a variance per some key, you would need to use a group by clause, just like you would if you want to Sum() or Average() your values.
In above sample, I haven't used another field to group by, so I don't need the group by clause (just like in your SQL).
You can use the same expression
as expression in a front end chart. Stdev() is a statistical aggregation function, hence you can use it same way you would use a Sum(), Avg() or Median().
Use one or more dimension(s) in your chart if you need to get your aggregate grouped by.
Ok, wondering if I can pick your brain on something a little more specific towards this. So the final calculation I'm trying to get is the Alpha Reliability of exam questions. Here's the sql that we use.
SELECT @itemVarianceAvg = AVG(is_item_variance)
SELECT VAR(i_item_score) AS is_item_variance
WHERE i_exam_form = @currentForm
GROUP BY i_item_name
) AS S
So here, what we're basically saying is get the variance for each ItemScore Grouped by their name. To put it in context. People can take exams, each exam can have multiple items associated with it, but they will always have a unique name. So, we are getting all of the variances grouped by item, then averaging that. That is the @itemVarianceAvg
Now after that, we get this alpha reliability number by so
SELECT @tempNumeric = ((CAST(@tempInt AS float) / (CAST(@tempInt AS float) - 1))
* (1 - ((@itemVarianceAvg*@tempInt) / POWER(@stDevScore, 2))));
First thing is you can assume I already know the @tempInt number. Also, I know the proper function for the @stDevScore, but I'm trying to wrap this all into one line and I'm not getting things right. Here's what I have is this
(Count(ItemMeasure)/Count(ExamMeasure) - (Count(ItemMeasure)/Count(ExamMeasure) - 1))
Also, I read up on variables a little, but I'm confused on how I could define say the @tempInt variable, since I have to resuse the line Count(ItemMeasure)/Count(ExamMeasure)
This leads away from the question how you can calculate variance in Qlik, but ok...
If I understand your request correctly, you are basically missing the part:
" get the variance for each ItemScore Grouped by their name. " And then average this list of values.
I think you already know how to do this in principle
=Avg ( Aggr( Sqr(Stdev( i_item_score )), i_item_name))
Hey, thanks for your help, the Aggr chunk got me headed in the right direction. Is it possible to declare variables at all in that fx window? I got the calculation working, but I have to reuse a lot of the same calculations, and was wondering if I can create a local variable in that window, and just use it. This is it
(Count(ItemMeasure)/Count(ExamMeasure) / (Count(ItemMeasure)/Count(ExamMeasure) - 1)) *
(1-(((Avg(Aggr(Sqr(Stdev(ItemScore)), ItemName))) * (Count(ItemMeasure)/Count(ExamMeasure))) / (pow(stdev(ExamScore), 2))))
And so, for example, if there was a way for me to set Count(ItemMeasure)/Count(ExamMeasure) to a variable named say, @count (or however I'd declare it), it'd be a lot cleaner
(@count / @count - 1)) *
(1-(((Avg(Aggr(Sqr(Stdev(ItemScore)), ItemName))) * @cunt) / (pow(stdev(ExamScore), 2))))
You can define a variable (not in the expression window, but either in the script or in the variable overview in the UI).
For example, add to your script:
SET vCount = Count(ItemMeasure)/Count(ExamMeasure);
Then you can use the variable in your chart expressions:
=($(vCount)) / ($(vCount) - 1)
To get you up and started, I would recommend that you go through some free tutorials that Qlik offers
or since most of the language is shared with QlikView
Or grab a good book