Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Need help with expression, targets made per employee per year

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

1 Solution

Accepted Solutions
prodanov
Contributor

Re: Need help with expression, targets made per employee per year

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

2 Replies
prodanov
Contributor

Re: Need help with expression, targets made per employee per year

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

Not applicable

Re: Need help with expression, targets made per employee per year

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.

Community Browser