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.
