Subtract value from first occurence of data

[jamestaylor52]

Hello,

I am working with a graph of Order Date (x axis) and cumulative order value (y axis). This is for a monthly recurring subscription service. I need to subtract a cost (the CPA) from the first instance of the customer ID appearing in the data.

My current method subtracts the CPA from every instance of the customer ID, rather than just the first instance. There should only be 1 CPA per customer, so sum(CPA) = [CPA]* Distinct ( Count ( customer_id ) )

This is my current expression:

rangesum ( above ( ( sum ( [OrderValue]) - ( [CPA]* Distinct ( Count ( customer_id ) ) ) ) ,0,rowno()))

The X-axis is OrderDate, which for a subscription service means the customer ID appears multiple times (as there is 1 order every month).

For example, if CPA = 5

OrderDate	OrderValue	Actual Order Value (the one I want to display)
01/01/2018	20	15
04/02/2018	20	20
03/03/2018	20	20
05/04/2018	20	20

etc

Weirdly, we don't have order number in the data, so I can't simply use {$<[OrderNumber]={'1'}>}

Kind regards,

James

[olivierrobin]

hello

and what about using firstsortedvalue() to retrieve the 1st value based on date ?

[jamestaylor52]

Thanks Olivier.

As my graph charts by month, when I've tried firstsortedvalue() it has performed it on every month. So it leaves me as the same result as Distinct ( Count ( customer_id ) ).

[jamestaylor52]

To update, I believe I've worked it out using aggr() function.

(count(aggr(min([order_date.autoCalendar.YearMonth]),customerID) returns a value of 1 at the first month the customer ID appears. This works perfectly!

James