Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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