2 Replies Latest reply on Sep 28, 2018 11:27 AM by Hari Ankem

    Trouble with 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) }}

      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!

       

      Robertino