Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
J
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
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
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!
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
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.
Hi
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
I don't think you need the sum's.
Try;
stdev(((a)+b))/(c))
Steve
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
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
I've attached a model for you to have a look at with some examples
can u resend the model i cant see it
Let me know if you can open this one. I am assuming you have a license and not using personal edition