Need help with expression, targets made per employee per year

[#1]

Hello,

I am trying to create a graph that will show the amount of targets made per employee per year.

I came as far as the expression

Sum(if(Sum(ORDER_unit_sale_price * ORDER_quantity)>= TARGET_sales_target, 1))

and I am using the dimensions TARGET_YEAR_nr, SALES_STAFF_code and PRODUCT_number.

Somehow nothing will show up in my object. I think I have to use the aggr function but I don't know very well how it works..

I did get the total targets to show stacked with the actual sales by putting two expressions:

Sum(ORDER_unit_sale_price*ORDER_quantity)

and

Sum(TARGET_sales_target)

with dimensions YEAR_nr and SALES_STAFF_code

I attached my qv file.

Thanks in advance for any help

[prodanov · Accepted solution]

change first formula to:

Sum(aggr(if(Sum(ORDER_unit_sale_price * ORDER_quantity)>=sum(TARGET_sales_target), 1),YEAR_nr,SALES_STAFF_code,PRODUCT_number))

you can't use aggregation function like sum,avg and tc into aggregation function. You must use function aggr() with aggregation by all dimensions

[#3]

Thank you very much. I am still not 100% sure on how the aggr function works, but at least I can replicate this into other expressions I need to make.