help with set analysis + expression

[#1]

Hi Guys,

I have 2 reference lines in my bar chart. The one displays the minimum expeses per FTE the other the average expenses over all companys.

I created the minimum reference using the following expression:

= Min({<Company=>} Aggr(sum({<Company=>} if(Expenses>0,  Expenses))

/ sum({<Company=>}  if(Expenses>0,  FTE)),Company))

as you can see the company field selection is ignored.

Now I'm trying to get the total average of all the companys fixed as a refference line. But I find it difficult to generate the right expression.

I've attached a qv sample what I would like to achieve

Hope any one can help!

Cheers

Carter

[#2]

Hi stefan,

I found the solution. I had to add another set analysis in the expression below:

sum({<Company={"=sum({<Company=, YearMonth={$(=max({<YearMonth=>}YearMonth))}>} Expenses)>0"}>} Expenses).

Thanks for helping me with the previous posts, I really appricate it

Cheers!

Carter