1 Reply Latest reply: Mar 29, 2018 10:39 AM by Sunny Talwar RSS

    Sum of amount when date is minimum

    Benjamin Peter

      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:

       

      RowIDStatusDateAmount
      110011501.01.201710000
      210011501.01.201810000
      310011501.01.20165000
      420011001.06.20158000
      520011501.07.20158000
      620011501.08.20158000
      722211001.01.201710000
      822211201.01.201810000
      922211501.01.20165000

       

      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?