How to perform average on a calculated dimension?

[shempgracie]

Hello Qlik Experts,

I have a calculated dimension that is showing me the names of project managers who have a variance (Actual-Budget) within a given threshold. 

I then have a chart using this dimension (along with a slider) so that I can easily change my threshold, and see who the all-start project managers are vs. the non-all stars.

I wanted to have  KPI that shows the average variance of the FILTERED project managers in the calculated dimension. 

So for example, I change my slider to say "My threshold is 10% of variance" and currently my dimension will change to show me only the PMs that are within that variance.  But now, I want it to say "Oh, and out of those selected PMs, their average variance is X.XX%".

 I've tried so many formulas and can't get it to work.  My dimension is "CalculatedPM" and my measures are Actual, Budget, and Variance.

Any thoughts on how to do this?

Thanks!

Mike

[petter]

I would suggest to use the Advanced Aggregation function called Aggr().

It is not an aggregation function itself but is a helper function to create nested aggregations. 

So you might have to do something like this:

Avg( Aggr( <aggregation-expression> , dim1 , dim2 , ... )

[shempgracie]

Hi Petter,

I tried your solution doing the following:  Avg(Aggr((Sum([Variance])/Sum([Forecast])),[ProjectManager]))

This works, and I do get the average for all project managers in my dimension.  But now I want to tweak it so that it does the average on my calculated dimension I created (which is a dimension that only contains Project Managers that have a variance of < 10%).  When I replaced "ProjectManager" with my calculated dimension of "CalculatedPM", it did not produce anything.

For reference, my calculated dimension contains the following expression to filter out the project managers to only those that have a variance under 10%.

=Aggr( if( FABS((Sum([Actual])/Sum([Forecast])-1)*100) > 10, null(), [Project Manager] ), [Project Manager] )

Any thoughts on how to fix that? I basically just want to get an average of all the PMs in my calculated dimension if possible.

Thank you!!!

Mike

[petter]

Please note that a click on the like icon (thumbs up) on one or several of my suggestions would be appriciated as that is the only way that contributors get a credit on this forum. Even a solution marked as correct will not give a credit - unfortunately. So please instead or in addition to a written "thank you" click the thumbs up.... 🙂

First of all it is important that you wrap the Aggr() function in an outer aggregation function like for instance Avg(). The Aggr() function can return more than one value and if it does most visualization objects can't display that and will only display a dash (-).

Could you try to use a Count( Aggr( ..... ) ) just to see how many values you get?
You could also use the Concat( Aggr(.....) , ' / ') to return a string of the returned values... for debugging...

[petter]

if you have a test data subset I can use it would be so much quicker and easier for me to help you...