topic Re: STD in QlikView

STD

Anonymous — Sun, 22 Sep 2013 09:42:58 GMT

Hey all,

i'm trying to use the stdev() func on a straight table, my formula is :

stdev((sum(a)+sum(b))/sum(c))

but its not working .....

any ideas ?

thanks

Re: STD

Mon, 23 Sep 2013 06:15:32 GMT

Hi Jonathan,

From Qlikview Help :

Stdev : Returns the aggregated standard deviation of expression or field iterated over the chart dimension(s).

Expression must not contain aggregation functions, unless these inner aggregations contain the

total qualifier.

And you have Sum() inside your expression, which I think is the reason its not working.

Hope this helps!

Re: STD

sushil353 — Mon, 23 Sep 2013 06:41:18 GMT

Hi Jonathan,

It is not working due to aggregation function.

Expression must not contain aggregation functions, unless these inner aggregations contain the total qualifier. For more advanced nested aggregations, please use the Advanced Aggregation function in combination with calculated dimensions.

HTH

Sushil

Re: STD

whiteline — Mon, 23 Sep 2013 07:03:11 GMT

Hi.

Stdev() function assumes aggregation while your expression ((sum(a)+sum(b))/sum(c)) is agreagted already.

Actually it works but can't calculate stdev using one value.

Re: STD

jonathandienst — Mon, 23 Sep 2013 07:53:46 GMT

You can't nest a sum inside a stdev statement in QV. To nest aggregation fuinctions like sum and stdev, you need to use an aggr expression. But even with an aggr expression, this statement will not work as the inner expression returns a single value, bur stdev needs a list of values.

Perhaps what you need is just:

stdev((a + 1)/c)

If that is not what you need, you need to provide more information about what you are trying to do, and if possible, a sample of yout model.

Regards

Jonathan

Re: STD

Mon, 23 Sep 2013 08:22:42 GMT

I don't think you need the sum's.

Try;

stdev(((a)+b))/(c))

Steve

Re: STD

Mon, 23 Sep 2013 09:45:25 GMT

Hey mate,

Don't use the SUM and your expression will work fine. The function has as per the help file, the same limitations with regards to nested aggregation as that of the AVG function. For this reason, your expression cannot contain a sum within a Standard deviation. It also makes sense since the standard deviation applied is a sample one, meaning that the sample is divided by n - 1. If you divide by a sum, your sample is 1 - 1, which returns 0, and dividing by 0 is obviously undefined.

The standard deviation function even if you have date as a dimension will return the deviation across the data set unless a 'set' of dates is selected. No selections assume full data model and will not populate for each date as each date would be the same.

If you have in your data model the following fields

Date
Week
A
B
C

and wanted to apply standard deviation to a date or week. Week will populate when added as a dimension as it is not the lowest level and has 7 assumed days within it to calculate variance. If you wanted to say calculate the standard deviation of all weeks, change your expression total mode to 'Std dev of Rows'.

Hope the above answers your concerns around the use of this function

Cheers,

Byron

Re: Re: STD

Mon, 23 Sep 2013 09:55:00 GMT

I've attached a model for you to have a look at with some examples

Re: STD

Anonymous — Tue, 24 Sep 2013 06:41:20 GMT

can u resend the model i cant see it

Re: Re: STD

Tue, 24 Sep 2013 06:55:26 GMT

Let me know if you can open this one. I am assuming you have a license and not using personal edition

Re: STD

sohailansari201 — Fri, 26 Jun 2015 18:03:02 GMT

Thank you.