Calculating values grouped by

[#1]

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]

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

OR

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

[#3]

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]

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

[#5 · Accepted solution]

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.