Conditional aggregation

[MikaelL]

Hi everyone,

I would like to build a chart with 1 dimension (Year) and  2 dynamic / calculated measures

Dimension: Year

Measures:

* Sum of revenue (condition = 'new customers')

* Sum of revenue (condition = 'existing customer'

The logic of 'new' customer is the first year where we first invoice. (E.g. if first year of Invoice = 2017, then this is the base year of 'new customer' and every year after 2017 the customer count as 'Existing customer'.

I created an expression to find first invoice date (works fine).

aggr({$<Version={'Actual'}, Type={'Revenue', 'SalesOrderStock'}>}Min([Year]),[GlobalName])

However, I failed to write the final expression which should be a conditional aggregation which summarize all invoices that are made in the same Year as when the first Invoice appeared in, like:

Sum( {$<Year = {"<='

aggr({$<Version={'Actual'}, Type={'Revenue', 'SalesOrderStock'}>}Min([Year]),[GlobalName])

'"}>} AmountEUR )

Current output is: 0.000 in all rows (table).

Any help much appreciated! 🙂

Kindly,

Mikael