Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sadiaasghar
Contributor
Contributor

calculate average of variable in qlikview

I want to calculate average of sum of all projects but it's giving me nested aggregation error. I have tried using total but it's giving me 0 as a result.

=Num(sum({<year_month_num = {$(=Max(year_month_num))}>}aggr($(=[Overall Step Weightage Formula]),Project,Month)),'#0.##%')

Please help me with this @sunny_talwar @Kushal_Chawda @brett 

6 Replies
Vegar
MVP
MVP

You will get nested aggregation errors when trying to do an aggregation inside an aggregation (without using aggr() in between. Example: sum({$} avg()) 

Right now your expression looks like this

Num(Sum({} aggr( %unknown%)))

As long as %unknown% is a normal aggregation such as sum(), max () or avg() then your expression setup should be fine and you should not get that nested aggregation warning.

So please check your inner dollar expanded expression to see if the error lies there. 

sadiaasghar
Contributor
Contributor
Author

Below Expressions are giving me error. Can we apply average directly on variable?

(sum(Avg({<year_month_num = {$(=Max(year_month_num))}>}aggr($(=[Overall Step Weightage Formula]),Month))))*100

or

(sum(Avg({<year_month_num = {$(=Max(year_month_num))}>}aggr($(=[Overall Step Weightage Formula]),Month))))*100

Vegar
MVP
MVP

What's the difference between the two?

What is the content of [Overall Step Weightage Formula]? 

sadiaasghar
Contributor
Contributor
Author

I am attaching sample .qvw file. And secondly I want variable value to calculate on the basis of Measure/Department but this formula only calculating value for Sub-Projects. Please have a look

sadiaasghar
Contributor
Contributor
Author

.QVW sample file is attached in above comment. Overall Step Weightage Formula is different for each sub-Project. Overall Step Weightage formula is a set expression itself. For Example for Sub-Project NFR-EV charger , Overall Step Weightage Formula is 

num(
sum(aggr(RangeSum(Above(TOTAL IF([Progress Description] <> ' ',If(Sum(Aggr(If(Match([Progress Description], [Sub Activities]), 1, 0), [Progress Description],[Sub Activities])) > 0,[Sub Activities Weightage]*1,0),0) , 0, RowNo(TOTAL))),[Sub-Project], Month,[Fiscal Year]))
,'0.##%')

sadiaasghar
Contributor
Contributor
Author

@Vegar Please look into the attached .qvw file.