I need to create a new dashboard that shows customer revenue but also calculates customers gained and lost.
The table would be on a running scale by month, and revenue would be totaled at the bottom for all customers.
To Calculate customers won or lost I would compare the value to the value in the cell in the previous column to the left if the current months value is greater than zero and the previous months value is zero then this is a customer gained, if the current months value is zero and previous months value is greater than zero then this is a customer lost.
In the example about we see that “Client 2” had revenue in Jan and no revenue in Feb so the client is lost, “Client 4” had no revenue for Jan and Feb but did have revenue in March so this is a client gained.
and we see that in April we lost “Customer 1” and gained “Customer 5”
Can you please share more information regarding the data granularity of the data at the source?
The approach that I would suggest you to try is :
- Create a calculated field to check firstValue of date for a client is equal to date field and return 1. This will give month wise number of clients addedd
- for clients lost check lastValue of date for a client is lesser than date value and return 1
We need to effectively use firstValue() and LastValue() function for this.
Thank you for your response.
I think this could work maybe with some adjustments, for calculating clients added I think this approach would be pretty simple.
Because we dont remove client records when they stop purchasing it would be more difficult.
When a client stops being a client (i.e. they discontinue service) their record would remain intact, the only thing that would change is their monthly revenue would stop updating.
@rsanchez.85 ,
Is this topic still open at your end and you need help (upload a sample data to Arena ? If we do not hear back in the next 7 days, we will archive the question.
