topic Calculating values grouped by in QlikView

Calculating values grouped by

Thu, 06 Nov 2014 15:59:12 GMT

Hello,

I'm having a bit of an issue when i try to calculate per product within the dimension year.

product_nr | sale_price | year product_nr | sales_target | year

1 | 5 | 2004 1 | 10 | 2004

1 | 6 | 2005 1 | 5 | 2004

2 | 10 | 2004 2 | 3 | 2004

What i want as outpur is:

year | expected_sales

2004| 105

so 5*15+3*10+ etc.....

i have tried with aggr but no luck with this expression:

sum(Aggr(NODISTINCT sum(FORECAST_expected_volume) * ORDER_unit_price, PRODUCT_number))

I am problably doing something wrong. I would really appreciate some help.

MK_QSL — Thu, 06 Nov 2014 16:31:03 GMT

sum(AGGR(SUM(DISTINCT sale_price) * SUM(Distinct sales_target),product_nr))

sum(AGGR(SUM(DISTINCT sale_price) * SUM(sales_target),product_nr))

Thu, 06 Nov 2014 20:03:52 GMT

Thank you, i found this works but only when i select the year in a select box. I hope this image will show the new problem.

The value is correct at image 2, so 38,263,344.28 that is when i select the year in the list box.

At image 1, when i dont select anything, it shows 107,012,480.16 and that is not correct.

Should i add the year to the aggr? I dont know if that is possible.

MK_QSL — Fri, 07 Nov 2014 10:30:10 GMT

=SUM(Aggr(SUM({<year = {"=sum(sales_target) <> 0"}>}sale_price) * SUM({<year = {"=sum(sale_price) <> 0 "}>}sales_target),product_nr))

Fri, 07 Nov 2014 14:25:51 GMT

This expression still gives me wierd results. It is far to complicated for me to understand it to .

I used this expession to make it work.

sum(AGGR(ORDER_unit_price*sum(FORECAST_expected_volume),YEAR_nr,PRODUCT_number))

Thanks for your time, i really appreciate it.