Hello community,
I have already learned a lot from all of your earlier posts and answers but for my question today I got stuck and would like to ask for your help.
I have a table of historical customer data. The table includes the colums ID, Status, Date, and Amount. Each customer can have several rows. In my analysis I need the sum of the amount per customer of their first entry in the status 115.
As an example:
| Row | ID | Status | Date | Amount |
| 1 | 100 | 115 | 01.01.2017 | 10000 |
| 2 | 100 | 115 | 01.01.2018 | 10000 |
| 3 | 100 | 115 | 01.01.2016 | 5000 |
| 4 | 200 | 110 | 01.06.2015 | 8000 |
| 5 | 200 | 115 | 01.07.2015 | 8000 |
| 6 | 200 | 115 | 01.08.2015 | 8000 |
| 7 | 222 | 110 | 01.01.2017 | 10000 |
| 8 | 222 | 112 | 01.01.2018 | 10000 |
| 9 | 222 | 115 | 01.01.2016 | 5000 |
The relevant rows that have to be taken into the calculation would be the rows 3, 5 and 9 because for customer 100 the first entry in the status 115 would be in January 2016. For customer 200 it would be July 2015 and for customer 222 it would be January 2016 again.
So if I would take the sum for January 2016 the result would have to be 10.000.
I have already tried several ideas from the forum. One solution that gives me the correct count (in this example=2) is the following formula: count({$<STATUS={115}>} aggr(min ([Date]), [ID] )) but I can't figure out how to change the count into sum.
One other idea was to go with a calculated field and set a flag in the relevant rows (3,5,9). But i didnt find a way to compare the rows regarding minimum value.
So I'm counting on you 🙂 Do you have any idea how to solve this challenge?
