3 Replies Latest reply on Sep 28, 2018 11:23 AM by Okechukwu Ossai

    Please i need help with this - Calculation

    Robertino Bonora

      Good morning guys, I have an urgent problem with the project I'm working on and it's next:


      In the database I have a table that we will assume is called XXX, in this table the records have a column called Operations and for each of these operations I can have between 1 to 15 records, these records are opened from another field that we will call Status, this state is a numeric field with values from 1 to 15.


      There are also two other fields, one is Date and another that we will call Status_2 in which indicates if each operation is on that date is Open (Abierto), Closed (Cerrado), Returned (Devuelto) or Cancelled (Anulado).


      What I need to do is count the number of records in the database by analyzing it in the following way:


      For each one of the operations I need to obtain the MAXIMUM Status in a selected date range (In a selected range could have 15 records as maximum or less, I need to know which is the maximum number of Status, the range of dates for the moment is a parameter that I use as maximum date, then it would be all the information of the base until that date of that parameter). After obtaining the maximums per operation I only need to count the operations that have Status_2 with Open or Returned value and show that value.


      At the moment I have this calculation:


      IF { FIXED [Operations] : MAX(Status) } = [Status]

      THEN { FIXED [Operations] : COUNTD( IF [Status_2] = 'Open' OR [Status_2] = 'Returned') AND DATE([Date]) <= [Parameter_MAX] THEN [Operations] END) }}



      I hope I've been clear, please this problem is urgent, thank you very much,


      I enclose a workbook with an example. I managed to get the result from the database and an Excel and in theory should give me the value 631 if I have the date range Parametro_MIN = 2016-01-01 and Parametro_MAX = 2016-12-31.


      Thank you!