TOTAL sum of sum(aggr(sum(cost), year)) with condiditons

[thanhphongle]

Hello Community,

here I am again. I figured out some basics about the aggr() function but I still have some problems. I m describing my problem here:

What I have is

Variables

x = 1.500.000,00 €

y = 1.000.000,00 €

This is my tables:

contract_begin_year	cost_acquisition	Sales Percentage	< 100%?	Output y
2017	1.117.910,81 €	74,53 %	yes , return something else	745.273,87 €
2016	2.606.979,59 €	173,80 %	no, return y	1.000.000,00 €
2015	1.452.900,07 €	96,86%	yes, return something else	968.600,05 €
TOTAL SUM	5.177.790,47 €			2.713.873,92 €

for the column "cost_acquisition" I used the expression

-> Sum(Aggr(sum(cost_acquisition), contract_begin_year))

I know that I could just add sum(cost_acquisition), but I will need this expression later on in a text box

Now I added some condiditions like

if the Sales Percentage is < 100% it should calculate

-> Sum(Aggr(sum(cost_acquisition), contract_begin_year)) / x

if the Sales Percentage is > 100% it should return the variable y = 1.000.000 €

this is my expression:

= if(Sum(Aggr(sum(cost_acquisition), contract_begin_year))/x < 1, Sum(Aggr(sum(cost_acquisition), contract_begin_year))/x * y, y)

What I need the the total result of Output y = 2.713.873,92 € which I can write as an expression in a text box

I m still not fit into aggr() expressions or set analysis. Hope someone can help me out here.