topic Re: Equivalent to Variance Function? in Integration, Extension & APIs

Equivalent to Variance Function?

michael_andrews — Mon, 23 Jan 2017 22:46:48 GMT

I'm wondering if there is some sort of equivalent to the variance sql function? I.e., a way for me to get 1.66 from this data as you would if you ran this sql

CREATE TABLE #foo(f_val int)

INSERT INTO #foo VALUES(1), (2), (3), (4)

SELECT var(f_val) FROM #foo

Re: Equivalent to Variance Function?

swuehl — Mon, 23 Jan 2017 23:04:29 GMT

Try

LOAD Sqr(Stdev(Val)) as Var;

LOAD * INLINE [

Val

];

Re: Equivalent to Variance Function?

michael_andrews — Mon, 23 Jan 2017 23:05:46 GMT

So I'd have to add this to my load script? And if that's the case, how would I go about doing it on a column?

Re: Equivalent to Variance Function?

swuehl — Mon, 23 Jan 2017 23:09:37 GMT

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).

Re: Equivalent to Variance Function?

michael_andrews — Tue, 24 Jan 2017 13:57:23 GMT

I'll play with it some. I guess my bigger question was can I use that LOAD in the function of the measure, or do I have to add this aggregate thing as a new column in the data itself via the load script?

Re: Equivalent to Variance Function?

swuehl — Tue, 24 Jan 2017 14:11:19 GMT

You can use the same expression

=Sqr(Stdev( MEASUREFIELD))

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.

Re: Equivalent to Variance Function?

michael_andrews — Tue, 24 Jan 2017 15:06:04 GMT

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)

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

WHERE 1=1;

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))

*(1-((Sqr(Stdev(ItemScore))*Count(ItemMeasure)/Count(ExamMeasure)))/pow(stdev(ExamScore),2))

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)

Re: Equivalent to Variance Function?

swuehl — Tue, 24 Jan 2017 15:28:15 GMT

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

Average a Sum of Child Records

=Avg ( Aggr( Sqr(Stdev( i_item_score )), i_item_name))

AGGR...

When should the Aggr() function NOT be used?

Use Aggregation Functions!

Pitfalls of the Aggr function

Re: Equivalent to Variance Function?

michael_andrews — Tue, 24 Jan 2017 15:30:46 GMT

Well yea, my original variance question was me breaking this larger problem down into smaller portions. I'll look at this and get back to you. thanks.

Re: Equivalent to Variance Function?

michael_andrews — Tue, 24 Jan 2017 21:43:06 GMT

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))))

Re: Equivalent to Variance Function?

swuehl — Tue, 24 Jan 2017 22:00:38 GMT

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)

The Magic of Variables

The Magic of Dollar Expansions

To get you up and started, I would recommend that you go through some free tutorials that Qlik offers

New to Qlik Sense

or since most of the language is shared with QlikView

QlikView 11 Developer Tutorial

Or grab a good book

Books and literature

Have fun!

Re: Equivalent to Variance Function?

michael_andrews — Tue, 24 Jan 2017 22:10:00 GMT

Is the variable in the UI an extension? I vaguely remember reading something about that.

Re: Equivalent to Variance Function?

swuehl — Tue, 24 Jan 2017 22:18:37 GMT

No, I don't think so.

If you want to modify the variable in the UI as a user (accessing the app in the accesspoint), you need(ed?) an extension.

Re: Equivalent to Variance Function?

michael_andrews — Tue, 24 Jan 2017 22:23:50 GMT

This is the only variable thing I've ever known of, and we had to install it as an extension. http://i.imgur.com/Asjc2yl.png

Re: Equivalent to Variance Function?

swuehl — Tue, 24 Jan 2017 22:35:22 GMT

Enter edit mode of your app, then you can find the variable overview:

Re: Equivalent to Variance Function?

michael_andrews — Tue, 24 Jan 2017 23:13:40 GMT

Great, I'll check that out tomorrow. Thanks again for all your help!